SOLVED DBS211 – Introduction to Database Systems Lab 02

Objectives: The purpose of this lab is to familiarize you with tables, columns, relationships, and constraints as provided to you within the sample database. By the end of this lab you should be able to: • Produce a relationship diagram of an existing database • Visualize the sample database provided to you and display the relationships between the tables • Be familiar with the sample database that we will be using for the remainder of the term Preface: During this lab, you will need to create a relationship diagram. There are many free tools available to you for this task. One example would be http://draw.io. Explore the Database By navigating through SQL Developer and looking at the Columns, Data and Constraints tabs for each table, you will create a relationship diagram for all the tables in the database. Your diagram must include: • ZERO MARKS WILL BE GIVEN IF USING SOME OTHER SCRIPT DIAGRAMS. Answers cannot be combined for all the questions together as one diagram. (DBS211-script.txt should have been used from LAB01) • The names of the entities (tables) • The attributes (columns) for each table • Lines representing the relationships between tables, try to get the lines to as closely point to the correct fields as possible (not always possible to be exact, but do your best here). Do not overlap or cross the lines. • Crows Foot Symbols on the lines representing the type of relationship (1-1, 1-many) • Required fields should be bolded • Primary Key fields should be underlined or indicated with a PK beside it. • Child fields in the relationships should be indicated with an FK beside it. Example: LAB 02 – SUBMISSION You will be submitting Word document with the screenshots of a completed database relationship diagram of the following 5 questions. DBS211 – Introduction to Database Systems In the Word document header have your Name, Student ID number, section. This can be achieved by creating a simple box in the diagram before saving it. QUESTION 1 Find the primary keys and foreign keys for all the tables created in LAB 01. See an example below and follow the same way to find these keys from constraints tab in the tables Table Name Primary Key fieldname Foreignkey fieldname(Parent table name) RETAILEMPLOYEES EMPLOYEENUMBER OFFICECODE(RETAILOFFICES), REPORTSTO(RETAILEMPLOYEES) QUESTION 2 Find the cardinality of relationship between the parent and child tables from question1. For example compare the primary key value of parent table and foreign key values of child table. How many times it is used? Can there be many retailemployees in an retailoffice or 1 employee or 0 employee. Using the example below. You may want to find the cardinality on both sides of relationship, that means each relationship you will be reversing the table names to give the carinality. Table 1 name Table 2 name Cardinality RETAILOFFICE RETAILEMPLOYEES 1 TO MANY (EACH RETAILOFFICE HAS 1 OR MORE RETAILEMPLOYEES) RETAILEMPLOYEES RETAILOFFICE 1 TO 1 ( EACH RETAILEMPLOYEE BELONGS TO ONE RETAILOFFICE) QUESTION 3 Draw the Entity Relationship diagram of the following tables RETAILORDERS and Orderdetails QUESTION 4 Draw the Entity Relationship diagram of the following tables Orderdetails and RETAILPRODUCTS DBS211 – Introduction to Database Systems QUESTION 5 Draw the Entity Relationship diagram of the following tables RETAILORDERS and RETAILCUSTOMER