Data Modeling

Assignment 2

Questions

1. What is the difference between a database and a table?

  • A Database is a collection of information including tables, index, and data.
  • A Table is a collection of rows and columns to store the data, columns are the name and rows are the actual data information about the business.

2. What does a database expert mean when (s)he observes that a database displays both entity integrity and referential integrity?

  • Entity integrity means that the primary key for a row is unique, and does not match the primary key of any other row inside the table.
  • Referential integrity means that the relationships among joined tables remain consistent.

Ex: If you try to add a foreign key and the value doesn’t have a match in the parent table within the primary key column this cannot be allowed as it will be data that is not linked to both tables. Foreign key must have a null value or match the primary key from the parent table.

3. Why are entity integrity and referential integrity important in a database?

  • Entity integrity prevents duplications and lessens the amount of data required.
  • Referential Integrity is important to make sure the table remains consistent, while updating, deleting and simplifies the managing of your database.

4. Draw the basic entity relationship diagram for the database shown below.

The database below records which professors advise which students. A student is allowed only a single advisor. Professors may advise multiple students.

Question 4

5. Suppose that you have the entity relationship model shown below.

truckdiagram

During some time interval, a DRIVER can drive many different TRUCKs and any TRUCK can be driven by many DRIVERs

How would you convert this model into an entity relationship model that displays only 1:M relationships? (Make sure that you draw the revised entity relationship model.)

Question 5

6. How would you implement a 1:M relationship in a database composed of two tables? Give an example.

Question 6

7. Identify and describe the components of the database table shown in Figure Q2.70, using correct terminology. Identify the entity name, its attributes and the primary and foreign keys.

Figure Q2.70 The Database For Question 7
Figure Q2.70
  • Entity Name: Employee Name
  • Attributes: EMP_NUM, EMP_LNAME, EMP_INITIAL, EMP_FNAME, DEPT_CODE, JOB_CODE
  • Primary Keys: EMP_NUM
  • Foreign Keys: DEPT_CODE, JOB_CODE

8. Suppose that you are using the following database composed of the two tables shown in Figure Q2.11:

Figure Q2.11
  1. Identify the primary keys.
    • DIR_NUM, PLAY_CODE
  2. Identify the foreign key.
    • DIR_NUM
  3. Draw the Entity Relationship Diagram.
  4. Question 8 Relation

Problems

Use the database shown in Figure P2.1 to work problems 1 through 5. Note that the database is composed of four tables and reflects these relationships:

Figure p.21

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

An EMPLOYEE can have many BENEFITs, and any BENEFIT can be assigned to many EMPLOYEEs.

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

1. 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.

Problem 1

2. Draw the entity relationship diagram for the relationship between EMPLOYEE and JOB.

Problem 2

3. Do the tables exhibit entity integrity? Answer Yes or No, then explain your answer.

Problem 3

4. Do the tables exhibit referential integrity? Answer Yes or No, then explain your answer. Write NA (Not Applicable) if the table does not have a foreign key.

Problem 4

5. Draw the Entity Relationship diagram to show the relationships among EMPLOYEE, JOB, BENEFIT, and PLAN.

Problem 5

Use the database shown in Figure P2.2 to answer problems 6-8.

Figure P2.2
Figure P2.2

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

Problem 6

7. Do the tables exhibit entity integrity? Answer Yes or No, then explain your answer.

Problem 7

8. Do the tables exhibit referential integrity? Answer Yes or No, then explain your answer. Write NA (Not Applicable) if the table does not have a foreign key.

Problem 8

Problem 9

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

Problem 10

10. Do the tables exhibit entity integrity? Answer Yes or No, then explain your answer.

Problem 11

11. Do the tables exhibit referential integrity? Answer Yes or No, then explain your answer. Write NA (Not Applicable) if the table does not have a foreign key.

Problem 12

12. Identify the TRUCK table's candidate key(s).

  • TRUCK_NUM, TRUCK_SERIAL_NUM

13. Draw the entity relationship diagram for this database.

Problem 13

Use the database shown in Figure P2.40 to answer problems 14-15.

ROBCOR is an aircraft charter company that supplies on-demand charter flight services, using a fleet of four aircraft. Aircraft are always identified by a (unique) registration number. Therefore, the aircraft registration number is an appropriate primary key for the AIRCRAFT table.

The nulls in the CHARTER table's CHAR_COPILOT column indicate that a copilot is not necessarily required for some charter trips or for some aircraft. (Federal Aviation Administration rules require a copilot on jet aircraft and on aircraft having a gross take-off weight over 12,500 lbs. (None of the aircraft in the AIRCRAFT table is governed by this requirement; however, some customers may require the presence of a copilot for insurance reasons.) All charter trips are recorded in the CHARTER table.

Figure P2.40-1 Figure P2.40-2 Figure P2.40-3

Given this aviation database, work the follwing problems:

14. For each table, identify, where possible, the primary key, the foreign key(s) (if any), and the table they reference.

Problem 14

15. Create an Entity Relationship diagram. Diagram using the Crows foot model. Hint: Take a look at the table contents. You will discover that an AIRCRAFT can be used to fly many CHARTER trips, but that each CHARTER trip is flown by one AIRCRAFT. Similarly, you will discover that a MODEL references many AIRCRAFT, but each AIRCRAFT references a single MODEL, and so on.

Problem 15

After carefully exploring the database's contents, note the following relationships:

  • a CUSTOMER can request many CHARTER trips, but each CHARTER trip is requested by one CUSTOMER.
  • each CHARTER trip requires one AIRCRAFT, but an AIRCRAFT may be used to fly many different CHARTER trips.
  • each AIRCRAFT is of a certain MODEL and, because the charter company may have many aircraft of a given model, each MODEL may reference many AIRCRAFT.
  • a PILOT may pilot many CHARTER trips, but each CHARTER trip is piloted by one PILOT.
  • a PILOT may serve as a copilot on a CHARTER trip.
  • each PILOT is an EMPLOYEE (but not all EMPLOYEEs are PILOTS!)