PROG 110 – Database Management System Topic: Physical Database Design (Relational Model) 1. You are part of a team that is charged with designing a database for a local veterinary hospital. In consultations with the vets and their staff, you and your team develop the following requirements specification. • The hospital has several employees or staff. These employees are classified as veterinarians or veterinary technicians. For each employee, their Social Security Number, Full Name, Date of Birth, and School must be stored. • Clients make appointments for their pets. For each client, their Name, Date of Birth, Address, and Telephone Number must be stored. Clients are filed according to their Surnames. • Clients own pets. For each pet, the species, breed, and name must be stored, as well as the pet’s date of birth. • During appointments, veterinarians may recommend one or more tests to diagnose one or more problems with the pet. For each test, the test name, date administered and test result must be stored. The test result can be “positive”, “negative”, or “inconclusive.” Draw an ER Model and translate it into relational table schemas in MS ACCESS. Identify primary keys for each relation schema in terms of the attributes given explicitly in the scenario. The primary keys you identify should make sense according to the real world scenario (and thus may require brief justifications). In addition, specify foreign keys for each relation schema where appropriate. 2. Give an instance of your database schema from part (a) that encodes the following situation. • Dr. Frink works at the hospital. His SSN is 111-22-3333, he was born on 10/10/65 and obtained his veterinary degree from IU. • “Mrs. K” lives at “1 Sun Drive”. Her telephone number is 555-1122. She owns “Fluffy”, who is a 10 year old Angoran Persian (breed) Cat (species). • “Mr. L” lives at “2 Moon Street”. His telephone number is 555-3344. He owns “Fluffy”, who is a 5 year old China Poodle (breed) Dog (species). • On 10/05/04, Dr. Frink ordered a “Scale Test” for Mrs. K’s cat Fluffy, whereupon Fluffy tested positive for “Excessive Weight”.
Posted on: Fri, 23 Aug 2013 05:49:37 +0000
Trending Topics
Recently Viewed Topics
© 2015