MIS$3306$ Database Management Systems

Module $3$ Exercise

$40$ points

Required for Grading:

I am $($type your full name$)$ and I complete this assignment following the UHD academic integrity policy. $(5$ points$)$

Learning objectives:

$$ Describe the basic data$-$modeling building blocks Identify the relational model$$s basic components

$$ Describe the relational database model$$s logical structure $$ tables and their characteristics

$$ Identify appropriate entities and the relationships among the entities in the relational database model

Instructions:

$$ Use Microsoft Visio or Draw.io to draw the diagram. Diagrams made by any other tools will not be graded.

$$ Use IE $($crow$$s foot$)$ notation. Diagrams with any other notations will not be graded.

$$ The diagram should specify:

$$ entities,

$$ relationships with maximum numbers on each side, and

$$ relationship labels when appropriate.

$$ The diagram at this stage does not need:

$$ keys

$$ Identifying$/$non$-$identifying relationship specifications

$$ Add a textbox with your full name inside the diagram $($no point without your name$).$ The figure below is an example.

$$ Your answer is required when you see the red answer box like the box below.

Answer here:

$<>$

$$ Upload this document to Blackboard as your submission.

Questions and Answers:

$1.(18$ points$)$ Use the database shown in the figure below to answer the following questions.

a$.$ For each table, identify the primary keys and foreign keys. Write $$None$$ or $$NA$$ when there$$s no foreign keys.

Answer here:

Table

Primary Key

Foreign Key$($s$)$

EMPLOYEE

STORE

REGION

b$.$ Explain entity integrity. Do the tables exhibit entity integrity?

Answer here:

Entity integrity means $\_\_\_\_$

Table

Entity Integrity $($Yes$/$No$)$

EMPLOYEE

STORE

REGION

c$.$ Explain referential integrity. Do the tables exhibit referential integrity? Write $$None$$ or $$NA$$ when there$$s no foreign keys.

Answer here:

Referential integrity means $\_\_\_\_$

Table

Referential Integrity $($Yes$/$No$)$

EMPLOYEE

STORE

REGION

d$.$ Describe the relationship between STORE and REGION. The description should describe numbers to both tables, and in two directions. For example, one customer has many orders and one order belongs to one customer.

Answer here:

e$.$ Describe the relationship between EMPLOYEE and STORE. The description should describe numbers to both tables, and in two directions. For example, one customer has many orders and one order belongs to one customer.

Answer here:

f$.$ Create the data model for these tables and relationships.

Paste Your Answer Diagram Here:

$2.(18$ points$)$ Use the database shown in the figure below to answer the following questions.

a$.$ For each table, identify the primary keys and foreign keys. Write $$None$$ or $$NA$$ when there$$s no foreign keys.

Answer here:

Table

Primary Key

Foreign Key$($s$)$

STUDENT

ADVISOR

DEPARTMENT

b$.$ Explain entity integrity. Do the tables exhibit entity integrity?

Answer here:

Entity integrity means $\_\_\_\_$

Table

Entity Integrity $($Yes$/$No$)$

STUDENT

ADVISOR

DEPARTMENT

c$.$ Explain referential integrity. Do the tables exhibit referential integrity? Write $$None$$ or $$NA$$ when there$$s no foreign keys.

Answer here:

Referential integrity means $\_\_\_\_$

Table

Referential Integrity $($Yes$/$No$)$

STUDENT

ADVISOR

DEPARTMENT

d$.$ Describe the relationship between STUDENT and ADVISOR. The description should describe numbers to both tables, and in two directions. For example, one customer has many orders and one order belongs to one customer.

Answer here:

e$.$ Describe the relationship between ADVISOR and DEPARTMENT. The description should describe numbers to both tables, and in two directions. For example, one customer has many orders and one order belongs to one customer.

Answer here:

f$.$ Create the data model for these tables and relationships.

Paste Your Answer Diagram Here:

Review

$1.(4$ points$)$ Choose among the learning objectives of this module listed below and answer the question a and b$.$

$$ Identify the relational model$$s basic components

$$ Describe the relational database model$$s logical structure $$ tables and their characteristics

$$ Explain functional dependence, types of keys, and integrity rules

$$ Understand relational database operators that manipulate relational tables

$$ Identify appropriate entities and the relationships among the entities in the relational database model

$$ Explain data dictionary and its purpose

a$.$ Which learning objective have you learned the best? Why?

Answer here:

b$.$ What hands$-$on skill$($s$)$ have you learned?

Answer here:

$2$