Design - Relational Schema help!
Alright, I did most of the schema, but I would like someone else to edit it and make changes according to the business rules presented. I am a novice at this, so I feel like there are a lot of mistakes and I dont want to continue onto SQL till I have this right.
Business rules are as follows:
The company wishes to maintain data about employees. Data that it wishes to manage include SSN, name, home address, home telephone, date hired. pay to date, Fed FICA and VA withholding. There are three categories of employees: sales, management, and full time. Management employees have the unique attribute bonus percentage (based upon seniority) and sales employees have the unique attribute commission percentage. (Each sales personís commission per item is based upon seniority). Full time employees do not have any unique attributes and a given employee can belong to only one category of employee at a given time.
The company wishes to maintain data about customers. Data includes, customerís name, address, telephone number and email address.
Data about merchandise stored in the warehouse include the product ID, vendor name, vendor address, vendor telephone number, quantity on hand, description, color, unit price, and dimension.
As you review the forms and procedures of the organization, in an attempt to collect the business rules, you find that the organization uses a form called, Order. On this Order form you find the following data is collected: order identification number and order date. Customers can have many orders but each order belongs to one customer. You find that an Order form can contain many merhandise (items) and merchandise can be found on many Order forms. For each merchandise (item) purchased by a customer, the organization wishes to identify the quantity purchased, type of sale (cash, credit card or in house finance) and the salesperson responsible for the sale.
For those customers who apply to finance merchandise through in house financing the company wishes to manage the following data: date of application, approved/disapproved, amount financed, interest rate, balance, number of months financed, and amount overdue. Each customer may have one in house finance account (new purchases which are financed are added on to existing accounts) but each account must be assigned to exactly one customer.
My current relational schema:
EmpID (PK), SSN, FName, LName, Street, City, State, Zip,
Phone, Date_Highered, Pay_to_Date, FedFICA,
EmpID (FK), Bonus_Percent
EmpID (FK), Commission_Percent
Email (PK), FName, LName, Street, City, State, Zip,
ProductID (PK), Vendor_Name, Street, City, State, Zip,
Phone, Quantity_on_Hand, Descriptioin, Color, Unit_Price,
Alright, the rest of this is where I am really having trouble.
OrderID (PK), Order_Date, Email (FK)
MERCHANT + ORDER
ProductID_OrderID (PK), Quantity, Type_of_Sale,
FinanceID (PK), Date_of_Application, Approval,
Amount_Financed, Interest_Rate, Balance,
Number_of_Months, Amount_Overdue, Email (FK)
Thank in advance for any helpful input/editing!