As noted above, Happy Joe’s has about one hundred employees. The usual details such as name, address and phone number will be recorded. Employees must be at least 16 years old at the time they are first hired.
+ +Note that Service, Sales and Other are all subtypes of Staff, and therefore use the same primary key as Staff.
Hourly_Rate must meet minimum wage requirements: at least Total_Hours is measured to the nearest quarter hour and must be in the range 0–3500 (= 40 hours per week
Hourly_Rate must meet minimum wage requirements: at least Total_Hours is measured to the nearest quarter hour and must be in the range 0–3500 (= 40 hours per week
SalarySalary must meet minimum wage requirements: at least
Salary must meet minimum wage requirements: at least
Total_EarningsGross_EarningsSome sales staff are paid on commission (up to 30Car—see On_Commission is true and Commission_Rate must be greater than zero. If the salesrep is paid on flat rate then On_Commission is false and Commission_Rate must be zero.
Some sales staff are paid on commission (up to 30Car—see On_Commission is “true” and Commission_Rate must be greater than zero. If the salesrep is paid on flat rate then On_Commission is “false” and Commission_Rate must be zero.
Total_Earnings stores the total amount earned by a salesrep to date in the current financial year (1 April to 31 March). For salesreps who are on commission, this total is calculated from Commission_RateSale.Amount. For salesreps who are not on commission, the total is calculated from Car.Flat_Rate (via Sale—see
Gross_Earnings stores the gross amount (i.e., before tax) earned by a salesrep to date in the current financial year (1 April to 31 March). For salesreps who are on commission, this is the sum to date of Commission_RateSale.Amount. For salesreps who are not on commission, this is sum to date of Car.Flat_Rate (via Sale—see
Sales
Sales
Similar to Service_Staff above. We also accepted Other_Staff as a table name. Note that the paragraph above about Total_Earnings describes the process of how it is calculated, not a constraint on its possible values, so implementing this as a constraint is incorrect.
Similar to Service_Staff above. We also accepted Other_Staff as a table name. Note that the paragraph above about Gross_Earnings describes the process of how it is calculated, not a constraint on its possible values, so implementing this as a constraint is incorrect.
YearOdometerHappy Joe’s sell only modern cars, and so will not purchase anything manufactured before 1980. The value of Flat_Rate is determined by how frequently each particular type of car is sold (this information is not stored in the database); the “easier” it is to sell a car, the lower the value of Flat_Rate. All cars have a value for Flat_Rate because we do not know in advance whether they will be sold by a salesrep on commission or on flat rate. The value of Flat_Rate is independent of the sale price, which is why it is stored here rather than in Sale.
Happy Joe’s sell only modern cars, and so will not purchase anything manufactured before 1995. The value of Flat_Rate is determined by how frequently each particular type of car is sold (this information is not stored in the database); the “easier” it is to sell a car, the lower the value of Flat_Rate. All cars have a value for Flat_Rate because we do not know in advance whether they will be sold by a salesrep on commission or on flat rate. The value of Flat_Rate is independent of the sale price, which is why it is stored here rather than in Sale.
Cars may have any number of optional features, such as air conditioning, side airbags, body kit, iPod integration, cruise control, etc.
+Car_Feature is an associative entity that exists only to link cars with features. It has no additional attributes of its own.
Feature
DurationMax_AgeMax_KMDurationDistancePurchases are for a single car only (i.e., no bulk purchases). Trade-ins are effectively treated as a special type of purchase (see Sale below).
Purchases are for a single car only (i.e., no bulk purchases). Trade-ins are effectively treated as a special type of purchase (see