EntityRelationshipEntity Salesperson Customer Vendor Salesperson Customer assigned places

Entity-Relationship-Entity Salesperson Customer Vendor Salesperson Customer assigned places supply work purchases Car Order Invoice Customer Product Question: Discuss and give an example of the following types of associations: (1: 1), (1: M), (M: N)

A Library Example Given below is a table of data for a library. Normalize this data into the 3 rd normal form. The Library’s computer is programmed to compute the due date to be 14 days after the check-out. Student id Number Student First Name Student Last Name Book Call Number Book Title Date Out Due Date Number of Books Out This assumes that the call number is unique to the book, and more than one copy of the book may exist in the library & that all copies have same call number

Student id Number Student First Name Student Last Name Book Call Number Book Title Date Out Due Date Student(stu. ID, stu. FName, stu. LName, numof. Book. Out) Book(book. Call. ID, book. Title) Barrow(stu. ID, book. Call. ID, date. Out) Number of Books Out

A Veterinarian Example Given below are the tables of data for a veterinarian practice. Normalize this data into the 3 NF. Patient ID Patient Name Owner ID Owner Last Name Owner First Name Address 1 Address 2 Animal Code Animal Description Transaction Patient ID Service Code Service Description Date Service Charge

Service service. Code service. Desc service. Charge Owner owner. ID owner. Lname owner. Fname Address 1 Address 2 Transaction patient. ID service. Code service. Date Patient patient. ID patient. Name owner. ID animal. Code Animal animal. Code animal. Desc

A Faculty Example instructor Course 1 stu 2 stu 3 Course 2 stu 4 stu 5 What are the relationships? : intructor, course, student

Student stu. ID stu. FName stu. LName stu. Address Instructor ins. ID ins. FName ins. LName ins. Room ins. Phone Registration reg. ID stu. ID cs. ID grade C_section cs. ID course. ID ins. ID cs. Time cs. Room Course course. ID course. Name course. Desc

Formula 1 Racing Example Primary Entities: Drivers(driver. ID, driver. Name, driver. Age) Cars(car. ID, car. Maker, car. Engine. Size) Owners(owner. ID, owner. Name, team. Name) Pit. Crews(crew. ID, crew. Name) Races(race. ID, race. Name, race. Location) Relationships: Each car has one owner, Each owner may own many cars. Each driver may drive many cars. Each pit crew works on many cars. Each car has one pit crew. Each race may have many cars. Each car can enter many Races

Formula 1 Racing -Solution Drivers(driver. ID, driver. Name, driver. Age) Cars(car. ID, car. Maker, car. Engine. Size, driver. ID, owner. ID, crew. ID) Owners(owner. ID, owner. Name, team. Name) Pit. Crews(crew. ID, crew. Name) Races(race. ID, race. Name, race. Location) Entries(car. ID, race. ID)

Club Database Example Primary Entities: Members(mem. ID, mem. Name, mem. Email, mem. Phone) Commitees(comm. Code, comm. Name) Offices(office. Code, office. Name) Meeting_Type(meeting. Code, meeting. Description) Meeting(Date) Relationships: Each member may be on many commities, Each commitee may have many members. Each member may be an officer (but only once!). Each office is Occupied by only one member. Each meeting type occurs many times. Each Meeting is of one and only one meeting type. Attendance: Each meeting may have many members attending. Each member may attend may meetings

Club Database -Solution Members(mem. ID, mem. Name, mem. Email, mem. Phone) Commitees(comm. Code, comm. Name) Offices(office. Code, office. Name, mem. ID) Meeting_Type(meeting. Code, meeting. Description) Meeting(meeting. Date, meeting. Code) Comittee_Membership(comm. Code, mem. ID) Meeting_Attendance(meeting. Date, mem. ID)

Sales Database –Homework! Primary Entities: Customers(cust. ID, cust. Name, cust. Address, cust. Phone, cust. Contact) Sales. P(sales. PID, sales. PName, sales. PAddress, sales. PCommission. Rate) Cell. Phones(phone. Num, Make, Model) Products(product. ID, Product. Desc, Qty. On. Hand, Cost, base. Markup%) Invoices(Invoice. Num, invoice. Date, Delivery. Date) Relationships: Each customer works with one Salesperson. Each Salesperson works with many customer. Each Salesperson may have a Cell. Phone. Each Cell. Phone is assigned to one Salesperson. Each Customer may have many Invoices. Each invoice belongs to one customer. Each Product may appear on many Invoices. Each Invoice may be for many products

Sales Database -Solution Primary Entities: Customers(cust. ID, cust. Name, cust. Address, cust. Phone, cust. Contact) Sales. P(sales. PID, sales. PName, sales. PAddress, sales. PCommission. Rate) Cell. Phones(phone. Num, Make, Model) Products(product. ID, Product. Desc, Qty. On. Hand, Cost, base. Markup%) Invoices(Invoice. Num, invoice. Date, Delivery. Date) Relationships: Each customer works with one Salesperson. Each Salesperson works with many customer. Each Salesperson may have a Cell. Phone. Each Cell. Phone is assigned to one Salesperson. Each Customer may have many Invoices. Each invoice belongs to one customer. Each Product may appear on many Invoices. Each Invoice may be for many products