PROJECT PHASE# $1$

Project Title:

Car Dealership Management Database System

Project Description:

In this project, you are tasked with building a management database system for a car dealership. The purpose of this system is to facilitate the management of car sales and ownership details for its diverse range of cars. The dealership operates multiple branches in different cities, with each branch having a unique branch ID$,$ phone number, and address $($composed of city, state, and zip code$).$ Each branch is managed by one employee, who is identified by an employee ID$,$ name, phone number, email address, and date of birth. It is important to note that each manager is responsible for only one branch. While a branch may have multiple employees, each employee is assigned to only one branch. At each branch, there are cars available for sale. Each car has a unique license number, along with attributes such as make, model, year, and price. Each car must be associated with one and only one branch. However, a branch may have multiple cars for sale or none at all. Each employee is responsible for managing multiple cars, such as handling customer inquiries and test drives, marketing the cars to potential buyers, and negotiating sales agreements, with each car being managed by a single employee. A car may have one or more owners. Every owner is identified by an owner ID and owner name. It is possible for an owner to own one or more cars. The dealership also wants to keep track of the percentage ownership that an owner has for a given car. For example, if Faisal and Sarah both own a car, Faisal may own $60\%$ of the car while Sarah owns $40\%.$ This information is retained by the dealership and referred to as "percentage owned". In order to manage the registration details of cars sold by the dealership, we must link each car with its registration details. Each car registration is uniquely identified by its car ID and has registration date and registration expiration date. The dealership also manages insurance for the cars it sells. Each insurance policy is uniquely identified by an insurance ID and linked to a specific car. The attributes of each insurance policy include the type of insurance and its details.

Draw an ER diagram according to previous requirements that shows the Entities, Attributes and Relationships. Specify key attribute$/$s for each entity type $($if applicable$)$ as well as cardinality ratio and participation constraints for each relationship type.

$1$