data management system

CS631/101 Assignment1 Fall 2021

This assignment is due Monday 9/29/21 at 6pm. I will deduct 10 points for every day the assignment is late. I will not accept any submissions after I have reviewed it in class (10/6/21). Please state any assumptions you are making. Assumptions are required to be “real-world”, reasonable, and accurate.

Problem 1 (20 points)

Using what we know about relational databases, create two tables based on the date shown in the tables below. Do not use multi-valued attributes.

The first table should contain Student Name, Student Number, Course Name, Course Number, Section Identifier, and Grade. The second table should include Course number, Course name, Prerequite Course number and Prerequisite Couse name. Do not use multi-valued attributes. If the course does not have a prerequisite enter N/A in the applicable columns.

Problem 2 (15 points)

Given the following relationships complete the recursive relationship diagram shown below. For each worker show their relationship to their manager. Each non-manager must have a manager. Each manager must have at least 1 individual reporting to him.

Data: Use this notation to reflect that A1 reports to A2. (S=supervisor: E = Employee)

A1

A2

■R1

E

S

Manager	Employee
E5	E4, E2
E2	E7, E1, E6
E3	E5

E1

E2

E3

E4

E5

E6

E7

■R1

■R2

■R3

■R4

■R5

■R6

■R7

Note: I have included 7 relationships (R1, R2, …R7). If you do not feel you need all 7, you may delete those that are unused. Also note that supervisors are also employees.

Problem 3 (10 points)

Given the following information relating to the entities Teacher, Course, and Textbook:

i. A course can have at most 2 text books.

ii. A teacher may teach at most 5 courses.

iii. A course can have at most 1 teachers.

Complete the following:

A. Draw a simplified ER diagram for the example shown above. The diagram need only include Entities (relations) and Relationships (6 pts).

B. Show the cardinality constraint on each diagram(4 pts).

List any assumptions that you make that are pertinent to your decision. Assumptions are required to be “real-world”, reasonable, and accurate.

Problem 4 (15 points)

Given the following information concerning the relations Author, Conference, Paper

a. An author presents at conferences.

b. A conference uses papers

R1 = Author (AuthorName (FisrtName, MiddleName, LastName, ), AuthorID, Sex, University)

R2 = Conference (ConfID, ConfName, Location, Month, Yr)

R3 = Paper (PaperID, PaperTitle, AuthorID)

Create a simplified UML diagram for the information shown above. You only need to show the relations, the attributes for each relation, and the relationships between relations (min/max format) . Explain your assumptions, particularly regarding relationships.

Problem 5 (15 points)

Determine the cardinality ratio for each intuitive relationship shown below. (3pts each):

a. Passenger ____ Uber Driver(at a specific moment in time)

b. Chess Game ___ Chess Player

c. Classroom ____ Wall

d. Orchestra ____ Violin Player

e. Husband______ Wife (Assume a single moment in time, monogamous society, and all laws obeyed.) Explain your answers and list any assumptions you consider significant. Assumptions are required to be “real-world”, reasonable, and accurate.

Problem 6 (15 points)

In the database shown below (Figure 5.6) define the domain constraints that apply to these fields. The first item listed is the table name. The second item is the attribute. You can assume the database will never change (except for part E). Explain all answers.

A. Project/Pnumber

B. Department/Mgr_start_date (include separate explanation for YYYY, MM, DD)

C. Dept_Locations/Dnumber

D. Dependent/Relation (Assume dependent is part of immediate family)

E. Employee/Minit (Do not assume the database will never change. Think carefully about assumptions here. Hint – think musicians. )

Problem 7 (10 points)

Consider the schema below for a database named MOVIES. Using the constraints (cardinality) shown in the schema below, answer the following questions as True, False, or Maybe. Explain your answer.

a. There are some actors who have acted in more than a dozen movies.

b. A movie must have a minimum of three directors

c. A movie can have multiple producers.

d. Your instructor, Bruce Forman, has acted in multiple movies.

e. There are no actors in this database that have been in no movies.

2