HW #1 Answer Key

HW #1 Answer Key

#1.1 List four significant differences between a file-processing system and a DBMS

Answer:

A database coordinates the physical and logical access to the data; a file-processing system only coordinates physical access to the data
A DBMS reduces the amount of data duplication
A DBMS is designed to allow flexibility in what queries give access to the data, where a file-processing system only allows pre-determined access to data (by specific compiled programs)
A DBMS is designed to coordinate and permit multiple users to access data at the same time. A file processing system is much more restrictive in simultaneous data access

#1.2 This chapter has described several major advantages of a database system. What are two disadvantages?

Answer:

Setup of the database system requires much more knowledge, money, skills, and time
Complexity of the database may result in poor performance

#1.3 Explain the difference between physical and logical data independence

Answer:

Physical data independence is the ability to modify the physical schema without making it necessary to rewrite application programs. E.G., changing from unblocked to blocked record storage, or from sequential to random-access files.
Logical data independence is the ability to modify the conceptual schema without making it necessary to rewrite application programs. E.G., adding a new field to a record. An application program's view hides this change from the program.

#2.1 Explain the distinctions among the terms primary key, candidate key, and superkey

A superkey is any set of attributes such that the values of the attributes (taken together) uniquely identify one entity in the entity set.

A candidate key is a minimal superkey -- a superkey with no redundant attributes. In other words, if any one of the attributes is removed, the set of attributes that remain no longer form a superkey.

A primary key is one of the candidate keys, designated by the database designer.

Every primary key is also a candidate key; every candidate key is also a superkey, but not vice versa.

#2.2 Construct an E-R diagram for a car-insurance company whose customers own one or more cars each. Each car has associated with it zero to any number of recorded accidents

#2.3 Construct an E-R diagram for a hospital with a set of patients and a set of medical doctors. Associate with each patient a log of the various tests and examinations conducted.

#2.4 Construct an E-R diagram for the registrar's office. Document all assumptions you make about the mapping constraints.

Assumptions:

A class meets only at one particular place and time. This diagram does not attempt to model a class meeting at different places or at different times
There is no guarantee that the database does not have two classes meeting at the same place and time
Each class has a unique instructor

#2.5 Consider a database used to record the marks that students get in different exams of different course offerings.

(a) Construct an E-R diagram for the database modeling exams as entities and using a ternary relationship

(b) Construct an alternative E-R diagram that uses only a binary relationship between students and course-offerings. Make sure that only one relationship exists between a particular student and course-offering pair, yet you can represent the marks that a student gets in different exams of a course offering

#2.6 Construct appropriate tables for each of the E-R diagrams in exercises 2.2 to 2.4.

Answers:

For #2.2:

Vehicle(VIN, vehicle-make, vehicle-model, vehicle-year, License-plate)

Customer( customer-name, customer-address)

Accident-record( date)

Owns (customer-name, VIN)

Involved-in-accident (VIN, date)

For #2.3:

Patient(SS#, name, insurance)

Physician ( name, specialization)

Test-log( SS#, test-name, date, time)

Doctor-patient (physician-name, SS#)

Patient-history(SS#, test-name, date)

For #2.4:

Student(SS#, name)

Class(Course#, time, location, department)

Instructor(Iss#, name)

Teaches-class(Iss#, Course#, time, location)

Takes-class (SS#, Course#, time, location, grade)