Given the table structure shown below, answer the following questions: Project_M
ID: 3743448 • Letter: G
Question
Given the table structure shown below, answer the following questions:
Project_Manager
Project_Code
Project_Bid_Price
Manager_Address
Manager_Phone
Number of People Managed
Jen D. Smith
21-5z
1693843.00
33 Lee Rd., Gainsville, FL 37123
612-898-9909
57
Ed P. Prime
25-6T
837292.23
239 River Dr., St. Paul, MN 55234
904-227-1245
32
Sue Q. Perl
34-6R
9438373.00
874 Perl St., Dodge, WI 34976
823-772-1072
6
Jen D. Smith
32-8E
8472022.45
33 Lee Rd., Gainsville, FL 37123
612-898-9909
57
George F. Dorts
24-2Y
487592.65
216 Morton Rd., Stetson, TN 29185
305-911-3874
15
Ed P. Prime
23-9I
394854.00
239 River Dr., St. Paul, MN 55234
904-227-1245
32
1. How many tuples does the table contain? How many attributes are there per tuple?
2. What problem would you encounter if you wanted to produce a listing by state? How would you solve this problem by altering the table structure?
3. What data redundancies can you detect in the table above? Explain why data redundancy is undesired?
4. Give an example of update anomaly, based on the table above.
5. Give an example of delete anomaly, based on the table above.
Question 2 (2 Points): Explain the concept of physical data independence and its importance in database systems.
Question 3 (2 Points): Describe (use GOOD TABLE LAYOUT format/style from slides) at least 3 tables that might be used to store information in a Veterinary system.
Question 4 (6 Points): List three applications (other than social-networking) you have used that most likely employed a database system to store data. For each application, list:
some tables (at least 3, and use good table layout format/style) that you think were used in the application. So a total of 9 tables, 3 tables for each application.
some attributes for each table. At least 4 attributes per table, so a grand total of 36 attributes.
use the table layout format in Word.
Project_Manager
Project_Code
Project_Bid_Price
Manager_Address
Manager_Phone
Number of People Managed
Jen D. Smith
21-5z
1693843.00
33 Lee Rd., Gainsville, FL 37123
612-898-9909
57
Ed P. Prime
25-6T
837292.23
239 River Dr., St. Paul, MN 55234
904-227-1245
32
Sue Q. Perl
34-6R
9438373.00
874 Perl St., Dodge, WI 34976
823-772-1072
6
Jen D. Smith
32-8E
8472022.45
33 Lee Rd., Gainsville, FL 37123
612-898-9909
57
George F. Dorts
24-2Y
487592.65
216 Morton Rd., Stetson, TN 29185
305-911-3874
15
Ed P. Prime
23-9I
394854.00
239 River Dr., St. Paul, MN 55234
904-227-1245
32
Explanation / Answer
1. There are 6 tuples in the table . There are 6 attributes per tuple.
2. State is part of Manager_Address attribute , so it cannot be listed seperately. The table structure can be altered by seperating street address,state and pincode of the Manager in seperate attributes.
3. Data redundancies
4. update anomaly
If a project manager's address is changed , it has to be updated at all places in the table. If not updated at all places , inconsistency appears.
5. Delete anomaly
If a Manager left his job, the whole data about the Project will also get deleted from the database.
Question 2 . Physical Data Independence
Physical Data Independence makes sure that if we change the type of data storage , it should not affect the logical model or design of the database.
Question 3 Veterinary system.
VetDoctor(DoctorID,DoctorName)
Pet(PetID,PetName,Age)
PetOwner(OwnerId,OwnerName,PetID)
CheckUp(DoctorID,PetID)
VetDoctor,Pet,PetOwner and CheckUp are tables . Underlined are primary keys and italicized are foreign keys in the tables
Question 4 Three applications
School
Student(StudentID,StudentName,Age,Grade,CourseID)
Teacher(TeacherID,TeacherName,Experience,Department)
Course(CourseID,TeacherID,CourseName,Duration)
Hospital
Doctor(DoctorID,DoctorName,Speciality,Experience)
Patient(PatientID,PatientName,Age,Address,Phone)
Treatment(DoctorID,PatientID,Description,Cost)
Bank
Branch(BranchID,City,State,PinCode)
Customer(CustomerID,CustomerName,Address,Phone)
Account(AccountNumber,CustomerID,BranchID,Balance)
Do ask if any doubt. Please upvote.
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.