Database Help
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me



Go Back   Codewalkers ForumsOther TechnologiesDatabase Help

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Rating: Thread Rating: 2 votes, 2.50 average. Display Modes
 
Unread Codewalkers Forums Sponsor:
Old December 1st, 2012, 08:55 PM
Leopoldstotch Leopoldstotch is offline
Registered User
Codewalkers Newbie (0 - 499 posts)
 
Join Date: Dec 2012
Posts: 1 Leopoldstotch User rank is Just a Lowly Private (1 - 20 Reputation Level) 
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!

Reply With Quote
Old December 10th, 2012, 02:07 AM
leeshdyu leeshdyu is offline
Registered User
Codewalkers Newbie (0 - 499 posts)
 
Join Date: Dec 2012
Posts: 1 leeshdyu User rank is Just a Lowly Private (1 - 20 Reputation Level) 
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

Reply With Quote
Reply

Viewing: Codewalkers ForumsOther TechnologiesDatabase Help > Design - Relational Schema help!


Developer Shed Advertisers and Affiliates


Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump


Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 


Powered by: vBulletin Version 3.0.5
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.

© 2003-2014 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap