How to normalise this realtion From 1NF to 3NF Employee( em_id,name, school_No, Name, Phone, Position, wage) school_no foreign key School_no—>transitive the address from school to employee , teacher,student School (school_No,Address, size) Class(ClassID, Class_name, class_ Descrpt, teacher_id) teacher_id foreign key Student (student_id, Name, DOB, Phone , school_No) school_no foreign key Teach(teacher_id, student_id) both foreign key Take(classID, student_id) both foreign key Payment (PaymentID, paymentDate,Amount, student_id) student_id foreign key Teacher(emp_id ,certificate , subject, school_No) emp_id foreign key school_No foreign key

How to normalise this realtion From 1NF to 3NF Employee( em_id,name, school_No, Name, Phone, Position, wage) school_no foreign key School_no—>transitive the address from school to employee , teacher,student School (school_No,Address, size) Class(ClassID, Class_name, class_ Descrpt, teacher_id) teacher_id foreign key Student (student_id, Name, DOB, Phone , school_No) school_no foreign key Teach(teacher_id, student_id) both foreign key Take(classID, student_id) both foreign key Payment (PaymentID, paymentDate,Amount, student_id) student_id foreign key Teacher(emp_id ,certificate , subject, school_No) emp_id foreign key school_No foreign key

Systems Architecture

7th Edition

ISBN:9781305080195

Author:Stephen D. Burd

Publisher:Stephen D. Burd

Chapter3: Data Representation

Section: Chapter Questions

Problem 3RP

See similar textbooks

Similar questions

branch(branchhame, branch+citysassets} customer (HD, Customer name, customer street, customer city) loan loan number branch_name, amounty borrower(ID; loan_number account (account_number, branch name, balance)e depositor (HDs account number)- Figure le Consider the bank database in Figure 1, where the primary keys are underlined. Each branch might have many loans or accounts, associated with borrowers or depositors, respectively. Construct the following SQL queries for this relational database. 1. Find the name of each branch that has at least one customer who has an account in the bank and who lives in the "Harrison" city. Make sure each branch name only appears once. 2. Find the ID of each customer who lives on the same street and in the same city as customer "12345". (1) Please use "tuple variables". e (2) Please use "derived relations" or “with". 3. Find the ID of each customer of the bank who has an account but not a loan. 4. Find the total sum of all loan amounts for each branch…
Consider the following relation: TempID 111 222 333 444 555 666 TempName Joe Mike Sue Pat Bob Joe TemplD JobID J111 J222 J333 J444 J555 J666 TempName TEMP AGENCY Job TypelD T1 T2 T1 JobID T2 T1 T2 Job TypeDesc Bookkeepiing Answering calls Bookkeepiing Answering calls Bookkeepiing Answering calls • Each job has one job type, but multiple jobs can have the same job type • Each job is assigned to one employer. Same employer can have multiple jobs. 5a) In the figure below, indicate functional dependencies by drawing lines with arrows. -| Some of the requirements that this table is based on are as follows: • Each temporary worker has a unique TempID, and a non-unique TempName. • Each employer has a unique EID, and a non-unique EName. • Each job has a unique JobID, and a job type. • Each job type has a unique JobTypeID and a non-unique job type description (JobTypeDesc). • Each temporary worker is assigned to only one job. EID E1 E1 E2 E2 E2 E1 Job TypeID Job TypeDesc Ename Mid Bank Mid Bank…
Consider the following structure of an 'inventory Oracle database. cuelomer *** MUMERIC ($) VARCHAR 30) VARCHAR (15 DECIMAL) SALESMANJD NAME CUSTOMER JD ERC ($) CUST NAME RCHAR (30 CHAR (15 MUMERIC (3) CITY CITY COMMISSION GRADE SALESMAN ID NUMERIC ($) orders ORD NO NUMERIC (5) PURCH AMT DECIMAL (8,2) ORD DATE DATE CUSTOMER ID NUMERIC (5) SALESMAN ID NUMERIC (5) Find the highest purchase amount with customer ID of the one who made it, along with the order date; for those customers who have a higher purchase amount in a day within the range 2000 and 6000. Use the editor to format your answer 81
Normalize the following from UNF to 3NF. Animal_Adoption (AdoptionID, AdoptionDate, ICNo, FirstName, LastName, Contact, {AnimalID, AnimalName, AnimalType, AnimalRescueDate})
Given the business rule "An employee may have many degrees." Suppose that an employee has the following degrees. BA, BS, MBA, etc. These degrees could be stored in a single string as a multivalued attribute name EMP_DEGREE in an EMPLOYEE table such as the one shown below: EMP_NUM EMP_LNAME EMP_DEGREE 123 Carter BA, BS, MBA 124 Smith BBA, MBA 125 Jones AS 126 Doe BS, MS, PhD. This design is flawed, so correct it by creating additional table(s) and attributes(s) and show the relationships between them. Show all the data above in your table(s). Add additional data if new attributes were added. Show primary and foreign keys in your table(s).
A database has four transactions. Let min sup = 60% and min conf = 80%.cust ID TID items bought (in the form of brand-item category)01 T100 {King’s-Crab, Sunset-Milk, Dairyland-Cheese, Best-Bread}02 T200 {Best-Cheese, Dairyland-Milk, Goldenfarm-Apple, Tasty-Pie, Wonder-Bread}01 T300 {Westcoast-Apple, Dairyland-Milk, Wonder-Bread, Tasty-Pie}03 T400 {Wonder-Bread, Sunset-Milk, Dairyland-Cheese}(a) At the granularity of item category (e.g., itemi could be “Milk”), for the ruletemplate,∀X ∈ transaction, buys(X,item1) ∧ buys(X,item2) ⇒ buys(X,item3) [s,c],list the frequent k-itemset for the largest k, and all the strong association rules(with their support s and confidence c) containing the frequent k-itemset for thelargest k.(b) At the granularity of brand-item category (e.g., itemi could be “Sunset-Milk”),for the rule template,∀X ∈ customer, buys(X,item1) ∧ buys(X,item2) ⇒ buys(X,item3),list the frequent k-itemset for the largest k (but do not print any rules).
COURSE CRS CODE DEPT CODE CLASS V CLASS CODE CRS CODE CLASS SECTION CLASS TIME CLASS ROOM PROF NUM STUDENT V STU NUM STU INAME STU FNAME STU INIT STU DOB STU HRS STU CLASS STU GPA STU TRANSFER DEPT CODE STU PHONE PROF NUM ENROL V CLASS CODE 9 STU NUM ENROLL GRADE CRS_DESCRIPTION CRS_CREDIT Use the figure above on a school's database to answer the following questions: A. Identify each relationship type and write all of the business rules. B. Create the basic Crow's Foot ERD for the school C. Create the UML class diagram that reflects the entities and relationships you identified in the relational diagram.
Store the following fields for a library database: AuthorCode, AuthorName, BookTitle, BorrowerAddress, BorrowerName, BorrowerCardNumber, CopiesOfBook, ISBN (International Standard Book Number), LoanDate, PublisherCode, PublisherName, and PublisherAddress. A one-to-many relationship exists between publishers and books. Many-to-many relationships exist between authors and books and between borrowers and books.a. Name the entities for the library database b. Without creating a set of dependency diagrams, write the relational schema that meet 3rd NF requirements. c. Draw the Crow’s Food entity-relationship diagram for the library database.
Consider the following database instance. Table name: Students Primary key: sid Sid sname 7 Ricky 2 Ellen 6 MaryLou 4 Ellen Table name: Courses Primary key: cid cid cname 1 ICS 2 Finance Table name: Register Primary key: sid,cid Foreign key: sid references Students(sid) Foreign key: cid references Courses(cid) sid cid 7 2 2 2 7 1 4 1 5.1) For each of the following statements, show whether the statement is correctly executed or not (assume that the statements are executed in order, which means that if a statement is correctly executed, its effect is reflected in the following statement). If you say the statement is not executed, explain why. INSERT INTO Students VALUES (3, ‘Ellen’); INSERT INTO Students VALUES (6, ‘Ellen’); INSERT INTO Register VALUES (1, 2); INSERT INTO Courses VALUES (5, ‘Systems’); INSERT INTO Register VALUES (6, 5); INSERT INTO Register…
Can composite attributes be used in composite keys and vice versa?
Normalize the following UNF relation to 1NF, 2NF, 3NF, show primary key and foreign keys in all Normal Forms. a. StudentID SName SAddress CityID CityName GamelD Ahmad Musanna GName Football 600 GDuration JoinDate 18/05/2021 20/06/2021 123 Barka G19 1.5 G11 G19 Cricket 612 Bilal Barka 124 Sohar Football 1.5 25/06/2021 G15 Vollyball 02/12/2021 b. Consider the below Enhanced Entity relationship diagram and answer the following questions. PBox City PName JoiningDate PerlD Degree Address FAMILY Joinsin PERSON EemilyName StudID EmplD Date_of Birth Age Manager EMPLOYEE STUDENT Manage Manages
Consider the following database instance. Table name: Students Primary key: sid Sid sname 7 Ricky 2 Ellen 6 MaryLou 4 Ellen Table name: Courses Primary key: cid cid cname 1 ICS 2 Finance Table name: Register Primary key: sid,cid Foreign key: sid references Students(sid) Foreign key: cid references Courses(cid) sid cid 7 2 2 2 7 1 4 1 5.1) For each of the following statements, show whether the statement is correctly executed or not (assume that the statements are executed in order, which means that if a statement is correctly executed, its effect is reflected in the following statement). If you say the statement is not executed, explain why. INSERT INTO Students VALUES (3, ‘Ellen’); INSERT INTO Students VALUES (6, ‘Ellen’); INSERT INTO Register VALUES (1, 2); INSERT INTO Courses VALUES (5, ‘Systems’); INSERT INTO Register VALUES (6, 5); INSERT INTO Register…