|
 |
|
Codewalkers Forums
> Other Technologies
> Database Help
|
Design - Relational Schema help!
Discuss Relational Schema help! in the Database Help forum on Codewalkers. Relational Schema help! General SQL help. Help with MySQL, MSSQL, Oracle, mSQL, PostgreSQL, etc.
|
|
|
|
 |
|
|
|
|

Codewalkers Forums Sponsor:
|
|
|

December 1st, 2012, 08:55 PM
|
|
Registered User
|
|
Join Date: Dec 2012
Posts: 1
Time spent in forums: 4 m 21 sec
Reputation Power: 0
|
|
|
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:
EMPLOYEE
EmpID (PK), SSN, FName, LName, Street, City, State, Zip,
Phone, Date_Highered, Pay_to_Date, FedFICA,
VA_Withholdings, Employee_Type
MANAGEMENT
EmpID (FK), Bonus_Percent
SALES
EmpID (FK), Commission_Percent
CUSTOMER
Email (PK), FName, LName, Street, City, State, Zip,
Phone
MERCHANDISE
ProductID (PK), Vendor_Name, Street, City, State, Zip,
Phone, Quantity_on_Hand, Descriptioin, Color, Unit_Price,
Dimensions
Alright, the rest of this is where I am really having trouble.
ORDER FORM
OrderID (PK), Order_Date, Email (FK)
MERCHANT + ORDER
ProductID_OrderID (PK), Quantity, Type_of_Sale,
EmpID (FK)
FINANCE ACCOUNT
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!
|

December 10th, 2012, 02:07 AM
|
|
Registered User
|
|
Join Date: Dec 2012
Posts: 1
Time spent in forums: 6 m
Reputation Power: 0
|
|
|
UGG Boots Outlet
container attend said time is dear around the earth. Not only is its burn. And its construction operation to charm countless fans. can be faint in the winter, equal i summer deteriorate in the Spring plus Autumn season. In bleak elements, lengthy fleece can uphold your feet, blocking the openair air-conditioning in direction to brand the complete mass feel warmer. welcome to
|
Developer Shed Advertisers and Affiliates
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
Rate This Thread |
Linear Mode
|
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|