Consider the following relation which has been designed to store data about medical doctors, clinics, patients, and appointments.Important Assumptions:The patient is given an appointment with a doctor.The patient always visits the same clinic, but the doctor works in different clinics.DocPatientAppointments TableDrNamePatientNameAbdulla AliPatientAge ClinicIDAppDate15-Jun-21DrIDDrRankPatIDCIBlockD18John SmithSeniorP7012104416-Jun-2115-Jul-21Qasim AhmedFatima SamiQasim AhmedSameer OmarPOS35C2904Ahmed Nasir ProfP93POSP81D597.C335035C290418-Jul-2162C335020-Sep-21a) Why is the above table un-normalized?b) One way to transform the above table into 1NF is to flatten the table? Briefly explain how the flattening process is done.c) identify all Functional Dependencies (FD) from the above table. Each of the FDs is going to represent a stand-alone table. Makesure none of the produced tables contains neither partial nor transitive dependencies. [Hint: there are 4 FDs).Use the editor to format your answer

Note: The solutions of the first two parts are shown below. Please repost the remaining subpart to…

DocPatientAppointments Table DrName John Smith ... PatientAge ClinicID CI C2 AppDate 15-Jun-21 DRID CIBlock DrRank Senior PatID PatientName D18 P70 Abdulla Ali 12 1044 Qasim Ahmed Fatima Sami POS 35 904 16-Jun-21 350 15-Jul-21 18-Jul-21 20-Sep-21 D59 Ahmed Nasir Prof P93 C3 POS Qasim Ahmed Sameer Omar 35 C2 904 P81 62 C3 350 a) Why is the above table un-normalized? b) One way to transform the above table into 1NF is to flatten the table? Briefly explain how the flattening process is done.

DocPatientAppointments Table DrName John Smith ... PatientAge ClinicID CI C2 AppDate 15-Jun-21 DRID CIBlock DrRank Senior PatID PatientName D18 P70 Abdulla Ali 12 1044 Qasim Ahmed Fatima Sami POS 35 904 16-Jun-21 350 15-Jul-21 18-Jul-21 20-Sep-21 D59 Ahmed Nasir Prof P93 C3 POS Qasim Ahmed Sameer Omar 35 C2 904 P81 62 C3 350 a) Why is the above table un-normalized? b) One way to transform the above table into 1NF is to flatten the table? Briefly explain how the flattening process is done.

Database Systems: Design, Implementation, & Management

12th Edition

ISBN:9781305627482

Author:Carlos Coronel, Steven Morris

Publisher:Carlos Coronel, Steven Morris

Chapter8: Advanced Sql

Section: Chapter Questions

Problem 18RQ

See similar textbooks

Similar questions

I need help with the following Consider a STUDENT relation in a UNIVERSITY database with the followingattributes (Name, Ssn, Local_phone, Address, Cell_phone, Age, Gpa). Note thatthe cell phone may be from a different city and state (or province) from thelocal phone. A possible tuple of the relation is shown below:Name Ssn Local_phone Address Cell_phone Age GpaGeorge Shaw 123-45-6789 555-1234 123 Main St., 555-4321 19 3.75William Edwards Anytown, CA 94539a. Identify the critical missing information from the Local_phone andCell_phone attributes. (Hint: How do you call someone who lives in a dif ferent state or province?)b. Would you store this additional information in the Local_phone andCell_phone attributes or add new attributes to the schema for STUDENT?c. Consider the Name attribute. What are the advantages and disadvantagesof splitting this field from one attribute into three attributes (first name,middle name, and last name)?d. What general guideline would you recommend for…
Consider the following relation which has been designed to store data about medical doctors, clinics, patients, and appointments. Important Assumptions: The patient is given an appointment with a doctor. The patient always visits the same clinic, but the doctor works in different clinics. DocPatientAppointments Table DrName John Smith ... PatientAge ClinicID Ci C2 C3 CIBlock ApnDate 1044 904 350 904 350 DRID D18 DrRank Senior PatID P70 PatientName Abdulla Ali 15-Jun-21 16-Jun-21 15-Jul-21 18-Jul-21 20-Sep-21 12 POS P93 Qasim Ahmed Fatima Sami 35 7 D59 Ahmed Nasir Prof 35 C2 Qasim Ahmed Sameer Omar PO5 P81 62 C3 a) Why is the above table un-normalized? b) One way to transform the above table into 1NF is to flatten the table? Briefly explain how the flattening process is done. C) Identify all Functional Dependencies (FD) from the above table. Each of the FDs is going to represent a stand-alone table. Make sure none of the produced tables contains neither partial nor transitive…
Suppose a single relation (table) is used to record the information about students’ enrollment in courses. We assume that each student may enroll in more than one course and one course is assigned to one instructor. All questions below are based on the following ENROLLMENT unnormalized table. ENROLLMENT StudentID StuName CourseCode CourseName Grade CreditHr Instructor InstrOffice 1021234 Ali 1102 1201 Database Internet A B 3 3 Zaid Sumaiya 5.01 5.05 1024131 Amina 1201 1401 Internet Operating System C B 3 3 Sumaiya Abdullah 5.05 5.10 1. What would be the First Normal Form (1NF) relation? Identify the primary key. 2. Identify the functional dependencies represented by the attributes shown in the ENROLLMENT table. Label partial or transitive dependency where applicable.
Consider the following relation. Instructor (ID , name, dept – name, salary) Department (dept – name , building ,budget) Classroom (building , roomno , capacity ) Teaches (ID , course – id , sec – id , semesters ) Course (course – id , title , dept – name , credit ) Student (ID , name dept – name ,fot – ored ) Takes (ID , course – id , sec – id, semester , year, grade) Write down queries expressed in SQL. 1) Find the name of all instructors who got the highest salary in their department. 2) Find the names of all the students whose total credits are greater than 100. 3) Find the course id and grades of all courses taken by any student. 4) Find all departments that have at least one instructor, and list – the name of the departments along with the number of instructor
Suppose relations R(A,B) and S(B,C,D) are as follows
Match each of the following Set relationship representation with its corresponding ERD representation
Fill in the matrix representation for the relation depicted in the arrow diagram. 1 2 1 2 3 1 Pick Pick Pick 2 Pick Pick Pick 3 Pick Pick Pick
Draw an ER model; An author writed a book . Every book has a title, ISBN, year, and price. each book may be authored by one or more author and each author may write one , or more than one book. For authors the database keeps the name, address and URL of their homepage. Each book has a publisher. The database keeps the name, address , and phone number adn the URL of their wedbsite. Each publishers publishes one or more books and each book is published by exactly one publisher. The warehouse stocks several books. Each warehouse has a code, address and phone number. The database records the number of copies stocked at various warehouse.
Consider a database that includes four relations Student, Staff, Program, Advisor Student BannerID FName LName PCode 8003778543 James Oloye PO01 8765990453 Paul Carter P002 8002786543 Anita Boris PO01 8307563321 Jane Eyre P001 Staff BannerID FName LName PCode 8014768954 Chimamanda Adichie PO02 8001876565 Paulette Davis P001 Program PCode PName PO01 Library Science PO02 Management and Information Science
Consider the following database schema for University Library database. A student can borrow many books and a given book can be borrowed by any student if it is available in the library. For each borrowing, the borrowing and return dates are registered in the database. Student (SID: long, CPR:long (unique), Name:string, tel:number, major:String, gender:Character {'F' or 'M'}) Book (ISBN:long Title:string (unique), Author:string) BorrowedItems(stSID:long, book|SBN:long, serialNo:int (unique), BorrowingDate:date, ReturnDate:date) Write SQL statements to: 1. List ISBN, Title, Author of all books that include the word 'Database' in their titles in a descending order of ISBN. 2. List SID, Name, and Major of all female students doing major in CS'and their name starts with 'S'. For the toolbar, press ALT+F10 (PC) or ALT+FN+F10 (Mac).
Suppose RR is a relation with attributes A1,A2,…,AnA1,A2,…,An. How many superkeys will RR have if a) The only candidate key is A1A1. b) The only candidate keys are A1A2A1A2 and A1A3A1A3.
Consider that a database contains the following relations PARENT(X, Y), and a relation BIRTH(X, B), where B is the birthdate of a person X. The following information is used for question (b), (c) that we have the following family tree

SEE MORE QUESTIONS

Recommended textbooks for you

Database Systems: Design, Implementation, & Manag…

Computer Science

ISBN:

9781305627482

Author:

Carlos Coronel, Steven Morris

Publisher:

Cengage Learning

Database Systems: Design, Implementation, & Manag…

Computer Science

ISBN:

9781285196145

Author:

Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel

Publisher:

Cengage Learning

Principles of Information Systems (MindTap Course…

Computer Science

ISBN:

9781285867168

Author:

Ralph Stair, George Reynolds

Publisher:

Cengage Learning

Principles of Information Systems (MindTap Course…

Computer Science

ISBN:

9781305971776

Author:

Ralph Stair, George Reynolds

Publisher:

Cengage Learning

Database Systems: Design, Implementation, & Manag…

Computer Science

ISBN:

9781305627482

Author:

Carlos Coronel, Steven Morris

Publisher:

Cengage Learning

Database Systems: Design, Implementation, & Manag…

Computer Science

ISBN:

9781285196145

Author:

Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel

Publisher:

Cengage Learning

Principles of Information Systems (MindTap Course…

Computer Science

ISBN:

9781285867168

Author:

Ralph Stair, George Reynolds

Publisher:

Cengage Learning

Principles of Information Systems (MindTap Course…

Computer Science