Question 3 (15 pts) Question 3 (15 pts) Question 4 (10 pts)

Question 3 (15 pts)

Question 3 (15 pts)

Question 4 (10 pts)

Question 4 (10 pts)

Question 6 (20 pts)

Question 6 (20 pts)

Management Information System 136:370:06/07

Question 1 (10 pts)

Consider two entities: Patients (ssn, name, age, address) and Clinic (clinicID, hospital name, address). Each of these entities have the obvious real-world meaning. Consider the relation “patient of” where each patient must be assigned to at least one clinic. Draw an ER for this description (and do not restrict anything more than what is described here).

In the previous question what is the role of “ssn” in each of the entries?

Question 2 (10 pts)

The Tiny College relational diagram shows the initial entities and attributes for Tiny College.

Identify each relationship type and write all of the business rules.

Use the database shown in following figure to work Problems (a)−(c). Note that the database is composed of four tables that reflect these relationships:

An EMPLOYEE has only one JOB_CODE, but a JOB_CODE can be held by many EMPLOYEEs.

An EMPLOYEE can participate in many PLANs, and any PLAN can be assigned to many EMPLOYEEs.

Note also that the M:N relationship has been broken down into two 1:M relationships for which the BENEFIT table serves as the composite or bridge entity.

For each table in the database, identify the primary key and the foreign key(s). If a table does not have a foreign key, write None in the assigned space provided.

Create the ERD to show the relationship between EMPLOYEE and JOB.

Do the tables exhibit entity integrity? Answer yes or no and then explain your answer.

Identify and discuss the serious data redundancy problems exhibited by the file structure shown in following table. (The file is meant to be used as a teacher class assignment schedule)

Given the file structure shown in the same table, what problem(s) might you encounter if building KOM were deleted?

Question 5 (15 pts)

Create an ERD based on the Crow’s Foot model, using the following requirements:

An INVOICE is written by a SALESREP. Each sales representative can write many invoices, but each invoice is written by a single sales representative.

The INVOICE is written for a single CUSTOMER. However, each customer can have many invoices.

An INVOICE can include many detail lines (LINE), each of which describes one product bought by the customer.

The product information is stored in a PRODUCT entity.

The product’s vendor information is found in a VENDOR entity.

Hint: The ERD must reflect business rules that you are free to define (within reason). Make sure that your ERD reflects the conditions you require. Finally, make sure that you include the attributes that would permit the model to be successfully implemented.

Using the descriptions of the attributes given in the figure, convert the ERD shown in Figure P6.1 into a dependency diagram that is in at least 3NF.

Question 7 (20 pts)

The database consists of three tables movie, actor and casting:

List the films where the yr is 1962 [Show id, title]

List all of the Star Trek movies, include the id, title and yr (all of these movies include the words Star Trek in the title). Order results by year.

List the films in which ‘Leonardo DiCaprio’ has appeared.

List all the people who have worked with ‘Tom Hanks’.