Create ERD based on the following tables. The underlines attributes are primary keys. The links are...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
Create ERD based on the following tables. The underlines attributes are primary keys. The links are connection between primary keys and foreign keys EMPLOYEE FNAME MINIT LNAME SSN BDATE ADDRESS SEX SALARY SUPERSSN DEPARTMENT ESSN DNAME PROJECT PNAME DEPT LOCATIONS DNUMBER DEPENDENT DNUMBER MGRSSN PNUMBER ESSN DLOCATION WORKS ON PNO DEPENDENT NAME PLOCATION HOURS SEX MGRSTARTDATE DNUM BOATE RELATIONSHIP DNO Question #4 Create ERD based on the following tables. The underlines attributes are primary keys. The links are connection between primary keys and foreign keys Book BookID BookName Publisher Name Publisher Name Address Phone Book_Loans BookID Branch Id LibraryBranch BranchID BranchName Address BookCopies BookID Branchid NoOfCopies Book_Authors BookID Card No AuthorName DateOut DueDate Borrower Card No Name Address Phone Question #3 Create the tables related to the following ERD. Determine the primary Keys and the foreign keys of each table. A1 A2 B1 B2 C1 C2 A 1 R1 N B C N R2 1 M AttOfR5 AttOfR4 R3 AttOfR3 N F1 F R5 E R4 F2 F3 N D N D1 D2 D3 F4 E1 E2 D5 D4 ▪ I ▪ I Each patient is being treated for one sickness only There is only one phone number in each room in the hospital Each doctor can do only do one treatment, but a treatment can be done by many doctors The treatment cost is fixed for each treatment Each doctor works in only one department, but a department can have many doctors Each department has 1 supervisor. This supervisor is just one of the physicians who works in that department A Patient is charged based on the treatment cost and number of days in hospital Note that not all the rooms in the hospital has patient at a particular time but all patient must be is some rooms. Further, only some of the physicians are supervising the departments in the hospital; however, all departments must be managed by some physicians. You may make any other assumption you think is necessary but you have to be very specific and realistic. You can add other assumptions but you are not allowed to change the above assumptions Do the following a) Change the ERD to tables b) Place the tables in 3rd normal form (if necessary) c) Revise the given ERD based on the normalized tables (if necessary) d) Write a script to create a database. Your script should create the tables and ensures that all constraints are set properly. Here is some information to create your tables using SQL. Depending on your normalization process, some of the following fields may not be in your final normalized table. DATA ITEM (COLUMN NAME) PatientID PhysID Room No. AdmitDate PatientName PatientAddress RoomPhone HospitalStayDays RoomRate AmountOwing PhysName PhysDept DeptSupervisorld TreatId TreatDesc TreatCost TYPE Numeric Integer Numeric Integer Numeric Integer Date Character Up to 50 Character Up to 200 Character Up to 8 Numeric Integer Numeric Decimal 10 with 2 decimals Numeric - Decimal 10 with 2 decimals Character Up to 50 Numeric Integer Numeric Integer Numeric Integer Character - Up to 200 Numeric - Decimal 10 with 2 decimals RESTRICTIONS > 100 and <= 999 Not null Not null >= 0 >= 30.00 and <=100.00 Not null >= 50.00 Question #2 Consider the following ERD Where PhysID PhyName PhysDept 101 2000 0000 PatientID RoomPhone Room No DeptSupervisorld RoomRate Physician cures Patient TreatCost TreatDesc Treatld Amount Owing HospitalStaydays PatientName PatientAddress AdminDate PatientId: It is the identification number of each patient PatientName: It is the name of the patient Patientddr: It is the address of the patient AdmitDate: It is the date when the patient is admitted to the hospital AmounOwing: The amount the patient owes based on his/her sickness after being discharged RoomNo: it is the room where the patient is kept in the hospital RoomPhone: The phone number in the patient's room HospitalStayDays: Number of days the patient would be in the hospital for treatment. RoomRate: The rate charged for every day the patient is in the room In the second table: PhysId: It is the identification number of each physician PhyName: It is the name of each physician PhysDept: It is the department id where physician works DeptSupervisorId: It is the id of the physician who is in change of managing the PhyDept. For example, suppose physician x works in department y. DeptSupervisorld is the id of the physician (not necessarily physician x) who in managing department y. Treatld is a number that represents the type of treatment the physician can do TreatDesc and TreatCost are Treatment description and treatment cost Each patient is assigned one doctor, but a doctor can have many patients There may be more than one patient in a room but each patient is kept in one room only Question #1 Consider the following data. Arrows show the functional dependency. Product Item Id Num Quantity Item Used Product Receipt Quantity Sales Description Description Number Sold Date The arrows in this question indicated the determination of two attributes. For example, the arrow that goes ProductID to Product Description indicates that ProductID determines the Product Description. This in turn means that ProductId can be considered as primary key for Product Description. a) Write the tables b) Place the tables in 3rd normal form (if necessary) c) Create ERD based on the normalized tables DATA ITEM (COLUMN NAME) d) Write a script to create a database. Your script should create the tables and ensures that all constraints are set properly. Here is some information to create your tables ProductId Item Num Quantity Used ItemDescription Product Description ReceiptNumber Quantity Sold SalesDate TYPE Numeric - Integer Numeric - Integer Numeric - Integer Character - Up to 200 Character - Up to 200 Numeric Integer Numeric Integer Date RESTRICTION Not null >0 Not null >= 0 Create ERD based on the following tables. The underlines attributes are primary keys. The links are connection between primary keys and foreign keys EMPLOYEE FNAME MINIT LNAME SSN BDATE ADDRESS SEX SALARY SUPERSSN DEPARTMENT ESSN DNAME PROJECT PNAME DEPT LOCATIONS DNUMBER DEPENDENT DNUMBER MGRSSN PNUMBER ESSN DLOCATION WORKS ON PNO DEPENDENT NAME PLOCATION HOURS SEX MGRSTARTDATE DNUM BOATE RELATIONSHIP DNO Question #4 Create ERD based on the following tables. The underlines attributes are primary keys. The links are connection between primary keys and foreign keys Book BookID BookName Publisher Name Publisher Name Address Phone Book_Loans BookID Branch Id LibraryBranch BranchID BranchName Address BookCopies BookID Branchid NoOfCopies Book_Authors BookID Card No AuthorName DateOut DueDate Borrower Card No Name Address Phone Question #3 Create the tables related to the following ERD. Determine the primary Keys and the foreign keys of each table. A1 A2 B1 B2 C1 C2 A 1 R1 N B C N R2 1 M AttOfR5 AttOfR4 R3 AttOfR3 N F1 F R5 E R4 F2 F3 N D N D1 D2 D3 F4 E1 E2 D5 D4 ▪ I ▪ I Each patient is being treated for one sickness only There is only one phone number in each room in the hospital Each doctor can do only do one treatment, but a treatment can be done by many doctors The treatment cost is fixed for each treatment Each doctor works in only one department, but a department can have many doctors Each department has 1 supervisor. This supervisor is just one of the physicians who works in that department A Patient is charged based on the treatment cost and number of days in hospital Note that not all the rooms in the hospital has patient at a particular time but all patient must be is some rooms. Further, only some of the physicians are supervising the departments in the hospital; however, all departments must be managed by some physicians. You may make any other assumption you think is necessary but you have to be very specific and realistic. You can add other assumptions but you are not allowed to change the above assumptions Do the following a) Change the ERD to tables b) Place the tables in 3rd normal form (if necessary) c) Revise the given ERD based on the normalized tables (if necessary) d) Write a script to create a database. Your script should create the tables and ensures that all constraints are set properly. Here is some information to create your tables using SQL. Depending on your normalization process, some of the following fields may not be in your final normalized table. DATA ITEM (COLUMN NAME) PatientID PhysID Room No. AdmitDate PatientName PatientAddress RoomPhone HospitalStayDays RoomRate AmountOwing PhysName PhysDept DeptSupervisorld TreatId TreatDesc TreatCost TYPE Numeric Integer Numeric Integer Numeric Integer Date Character Up to 50 Character Up to 200 Character Up to 8 Numeric Integer Numeric Decimal 10 with 2 decimals Numeric - Decimal 10 with 2 decimals Character Up to 50 Numeric Integer Numeric Integer Numeric Integer Character - Up to 200 Numeric - Decimal 10 with 2 decimals RESTRICTIONS > 100 and <= 999 Not null Not null >= 0 >= 30.00 and <=100.00 Not null >= 50.00 Question #2 Consider the following ERD Where PhysID PhyName PhysDept 101 2000 0000 PatientID RoomPhone Room No DeptSupervisorld RoomRate Physician cures Patient TreatCost TreatDesc Treatld Amount Owing HospitalStaydays PatientName PatientAddress AdminDate PatientId: It is the identification number of each patient PatientName: It is the name of the patient Patientddr: It is the address of the patient AdmitDate: It is the date when the patient is admitted to the hospital AmounOwing: The amount the patient owes based on his/her sickness after being discharged RoomNo: it is the room where the patient is kept in the hospital RoomPhone: The phone number in the patient's room HospitalStayDays: Number of days the patient would be in the hospital for treatment. RoomRate: The rate charged for every day the patient is in the room In the second table: PhysId: It is the identification number of each physician PhyName: It is the name of each physician PhysDept: It is the department id where physician works DeptSupervisorId: It is the id of the physician who is in change of managing the PhyDept. For example, suppose physician x works in department y. DeptSupervisorld is the id of the physician (not necessarily physician x) who in managing department y. Treatld is a number that represents the type of treatment the physician can do TreatDesc and TreatCost are Treatment description and treatment cost Each patient is assigned one doctor, but a doctor can have many patients There may be more than one patient in a room but each patient is kept in one room only Question #1 Consider the following data. Arrows show the functional dependency. Product Item Id Num Quantity Item Used Product Receipt Quantity Sales Description Description Number Sold Date The arrows in this question indicated the determination of two attributes. For example, the arrow that goes ProductID to Product Description indicates that ProductID determines the Product Description. This in turn means that ProductId can be considered as primary key for Product Description. a) Write the tables b) Place the tables in 3rd normal form (if necessary) c) Create ERD based on the normalized tables DATA ITEM (COLUMN NAME) d) Write a script to create a database. Your script should create the tables and ensures that all constraints are set properly. Here is some information to create your tables ProductId Item Num Quantity Used ItemDescription Product Description ReceiptNumber Quantity Sold SalesDate TYPE Numeric - Integer Numeric - Integer Numeric - Integer Character - Up to 200 Character - Up to 200 Numeric Integer Numeric Integer Date RESTRICTION Not null >0 Not null >= 0
Expert Answer:
Answer rating: 100% (QA)
Following is the required ERD for the given Schema Relationships used 1 Employee DEPT 11 There can b... View the full answer
Related Book For
Fundamentals Of Database Systems
ISBN: 9780133970777
7th Edition
Authors: Ramez Elmasri, Shamkant Navathe
Posted Date:
Students also viewed these databases questions
-
Distances (kms) between the depot and 7 markets to be visited are given below. Assume that the cost of transportation is equal to the distances between the locations. D 1 2 3 4 5 6 7 D 1 25 222 43 29...
-
Q.1.Consider the ER diagram shown below for part of a BANK database. Map the ER diagram into a RELATIONAL SCHEMA. Specify all primary keys and foreign keys. Q.2.Use the relational schema shown below...
-
Repeat Laboratory Exercises 8.34 through 8.38 in domain relational calculus (DRC) by using the DRC interpreter. Exercises 8.34 Specify and execute the following queries in relational algebra (RA)...
-
Why might cloud computing be greener than conventional computing alternatives? Research online and share examples suggesting that cloud firms could be less environmentally taxing than if a firm built...
-
At t = 0, an emf of 500 V is applied to a coil that has an inductance of 0.800 H and a resistance of 30.0 . (a) Find the energy stored in the magnetic field when the current reaches half its maximum...
-
Recall the ice cream and frozen yogurt problem from Example B. Enter these matrices into your calculator, and use them to find the long-run values for the number of students who choose ice cream and...
-
Of the cameras produced by a company, 8% have a flash problem, 12% have a focus malfunction, and 0.9% have both a flash issue and a focus malfunction. Find the probability that a randomly selected...
-
Drake Company produces a single product. Last years income statement is as follows: Sales (20,000 units) .....$1,218,000 Less: Variable costs .......812,000 Contribution margin ......$ 406,000 Less:...
-
The popularity of Joe's Deli has caused lines to form throughout the operation and customers to start grumbling about the length of time they must spend to get their shopping completed. Joe's process...
-
Calculate the following four reliability coefficients using the Pearson product-moment correlation formula, correlate the scores to determine the reliability coefficient (xx). Show your work. 3....
-
Suppose that one day all 330 million people in the US climb up on tables. At time t=0, we all jump off. The resulting shock, as we hit the Earth's surface, will start the entire Earth vibrating in...
-
Empowerment and teamwork of frontline service workers are cited as a key to success in service businesses but when is empowerment most effective? What are the proper boundaries for empowerment, the...
-
Utilizing our Franciscan Values, discuss why it is imperative for global managers to think ethically. What does it mean to be ethical? Why should companies be ethical? What is the incentive (or lack...
-
Explain the operation of airbags, tennis racquets, and rolling when you jump from a moving vehicle. Why do we bend our knees when we land?
-
Based on the information in the table below, calculate the break-even points in terms of sales value. Fixed Factory Overheads Cost 50,000 Fixed Selling Overheads Cost 15,000 Variable Manufacturing...
-
Explain why migration is an important social issue. Explain the role of business in migration that is, What do you think is the responsibility of business in migration and why it is important. On...
-
The speed of light in air is 3 x 10 ms, whereas that of the speed of light in water is 2.26 x 108 ms. What is the refractive index of water with respect to air? [CBSE Question Bank] (a) 2.64 (b) 1...
-
What does non-recourse financing mean?
-
What is entropy, and how is it used in building decision trees?
-
How do question answering systems work?
-
What is the public key infrastructure scheme? How does it provide security?
-
Isothermal compression efficiency can be achieved by running the compressor: (a) At a very high speed (b) At a very slow speed (c) At an average speed (d) At zero speed
-
Derive the expression of work done by the compressor in isothermal compression, adiabatic compression, and polytropic compression.
-
Maximum work is done in compressing air when the compression is: (a) Isothermal compression (b) Adiabatic compression (c) Polytropic compression (d) None of these
Study smarter with the SolutionInn App