[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 termPreface:
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.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 OrderdetailsQUESTION 4
Draw the Entity Relationship diagram of the following tables Orderdetails and RETAILPRODUCTSQUESTION 5
Draw the Entity Relationship diagram of the following tables RETAILORDERS and RETAILCUSTOMER