Cars To Drive: Is A Used Car Dealership Operating In The
Cars To Drive Ctd Is A Used Car Dealership Operating In the Sydney C
Cars To Drive (CTD) is a used car dealership operating in the Sydney CBD as a franchise business. They wish to enable their customers to view cars listed for sale online and to monitor which cars are being sold as well as current stock by each franchisee. CTD has contracted us to design a relational database that aligns with their operational needs and business rules.
The business operates six different dealership locations around Sydney, each managed by a single franchisee. Each location has a unique location code, name, street, city, state, postcode, and phone number. The franchisee associated with each location is identified by a unique franchisee number, name, contact details, and others. The franchises employ between four and eight staff members, including one manager who supervises all other staff at that location. The database must store details about all employees, with a special focus on the manager of each location.
Each location stocks various types of cars, with each type potentially including several ranges. For example, the type might be "Holden Commodore" with ranges like "Calais-V" or "Evoke". Each range is associated with only one type, but a type can have multiple ranges. The database should capture information about car types, ranges, manufacturing year, and their condition, for each location.
Furthermore, CTD needs to keep track of cars sold by each franchisee. For each sale, the database must record the type and range of the car, the sale date, and the salesperson responsible for the sale. A franchisee can sell many cars of the same type and range, and each sale record should link to the car and salesperson involved.
Design of the ERD including Entities, Attributes, Relationships, Cardinality, and Optionality
Entities and Attributes
- Location: location_code (PK), location_name, street, city, state, postcode, phone_number
- Franchisee: franchisee_id (PK), name, contact_info, location_code (FK)
- Employee: employee_id (PK), name, role, contact_info, location_code (FK), is_manager (Boolean)
- CarType: type_id (PK), type_name
- CarRange: range_id (PK), range_name, type_id (FK), year, condition, location_code (FK)
- Car: car_id (PK), range_id (FK), license_plate, status (Available/Sold), current_location_code (FK)
- Sale: sale_id (PK), car_id (FK), date_sold, salesperson_id (FK)
Relationships and Cardinalities
- Location to Franchisee: 1-to-1 (each location is operated by one franchisee; a franchisee operates only one location)
- Franchisee to Employee: 1-to-many (one franchisee employs 4-8 staff members)
- Employee to Manager: One employee per location is designated as manager; this can be represented via is_manager attribute, with a 1-to-1 relation designated by a 'manager' role
- Location to CarRange: 1-to-many (each location stocks multiple ranges)
- CarType to CarRange: 1-to-many (each type has multiple ranges)
- CarRange to Car: 1-to-many (each range can have many cars)
- Car to Sale: 1-to-1 (each car can be sold once; the sale module records which cars have been sold)
- Sale to Employee (Salesperson): many-to-one (each sale is made by one salesperson; sales are linked to the employee who performed the sale)
Optionalities
- Car's current_location_code is optional for sold cars or those being serviced; otherwise, it links to Location.
- For sales, only cars marked as sold will appear, linking to the salesperson who completed the sale.
Primary and Foreign Keys
- Location: Primary Key = location_code
- Franchisee: Primary Key = franchisee_id; Foreign Key = location_code
- Employee: Primary Key = employee_id; Foreign Key = location_code; role determines if employee is manager
- CarType: Primary Key = type_id
- CarRange: Primary Key = range_id; Foreign Keys = type_id, location_code
- Car: Primary Key = car_id; Foreign Key = range_id, current_location_code (optional)
- Sale: Primary Key = sale_id; Foreign Keys = car_id, salesperson_id
Conclusion
The proposed ERD effectively models the organizational structure, stock inventory, sales operations, and staff management of CTD. Clearly defining entities, attributes, relationships, and keys ensures robust data integrity and supports the business's operational reporting and online display needs. The design accommodates the business rules regarding locations, franchisee management, employee supervision, car types and ranges, stock, and sales tracking, providing a comprehensive and scalable database solution.
References
- Elmasri, R., & Navathe, S. B. (2015). Fundamentals of Database Systems (7th ed.). Pearson.
- Database Systems: A Practical Approach to Design, Implementation, and Management (6th ed.). Pearson.