Please help For each exercise you are required to build a se

Please help!!

For each exercise, you are required to build a set of tables normalized to 3NF. Tables should be described using the following notation:

TABLE(field1, field2, field3, field4, field5, …)

Using this notation, TABLE is the name of the table. Primary key fields are bold and underlined, like field1. Foreign key fields are italicized and required fields are in bold. Therefore, a required foreign key is in bold italics, like field2. An optional foreign key is in italics but not bold, as is field3. A primary key field that is also a foreign key is underlined, italicized, and bolded. Regular, non-key attributes are in bold when required, as is field4, and not in bold when optional, as is field5.

For each problem, use dependency diagrams to demonstrate the veracity of your solution.

A normalized solution should consist of a set of tables where each table represents a single entity. Each table should have a primary key composed of one or more fields. Where appropriate, tables should have foreign keys that establish relationships between them

Exercise 8: Jerkwater College has a selection of activities students can participate in throughout the school year. A given activity has a fixed fee. A student can sign up for as many activities as they like, but a student cannot sign up for the same activity more than once on a given day. ACTIVITY ( StudentID, StudentName, Activity, ActivityDate, Fee, AmountPaid )

Exercise 9: Jerkwater College has a selection of activities students can participate in throughout the school year. The fee for a given activity is subject to change throughout the year depending on factors such as the weather, time of year, and location of the activity. A student can sign up for as many activities as they like, but a student cannot sign up for the same activity more than once on a given day. ACTIVITY ( StudentID, StudentName, Activity, ActivityDate, Fee, AmountPaid )

Exercise 10: RoadKill Café has a variety of products in it’s gift shop that customers can purchase after eating their meal. The owner is not very bright, but he knows just enough about spreadsheets to be dangerous. He created one, big spreadsheet with the columns shown below. Every time a customer orders an item, he fills in the details of the order in the spreadsheet. He has noticed a lot of repetitive information in the spreadsheet. He has mistakenly put different email addresses in for the same customer and he can’t figure out which one is correct. He realizes the spreadsheet is probably not built properly and decides to hire someone to build a database for him. Here are the columns within his spreadsheet. ORDER ( Customer_Name, Customer_Address, Customer_Phone, Customer_Email, Order_Date, Order_Total, Product_Name, Quantity_Ordered, Product_Price, Prod_Qty_In_Stock, Supplier_Name, Supplier_Phone ) Create a series of tables normalized to 3NF to eliminate anomalies and reduce the amount of data duplication.

Solution

1)in the first normal form avoid repeatable and should have primary key

2)

3)it is in first normal form and second normal form

8)STUDENT(STUDENTID,STUDENTNAME)

ACTIVITY(ACTIVITYID,ACTIVITYDATE,ACTIVITY, STUDENTID )

PAYMENT(ACTIVITYID,ACTIVITYDATE,FEES,AMOUNTPAID)

9)

STUDENT(STUDENTID,STUDENTNAME)

ACTIVITY(ACTIVITYID,ACTIVITYDATE,ACTIVITY, STUDENTID )

PAYMENT(ACTIVITYID,ACTIVITYDATE,FEES,AMOUNTPAID)

10)

ORDER ( Orderid, Order_Date, Order_Total, ,Quantity_Ordered,SupplierId,customerid)

Product(Productid,Product_Name,Product_Price, Prod_Qty_In_Stock)

ProductOrder(Orderid,ProductId)

Customer(customerid,Customer_Name,Addressid )

Customer_Address(Addressid, Add_line1,Add_line2,Customer_Phone, Customer_Email ,city,state,zip,country)

unique   

Supplier(Supplierid,Supplier_Name, Supplier_Phone )