Purpose of Experiment purpose and Requirements

Transform the following ER diagram into Relations and Create the required tables using Oracle / MySQL: (with Primary Key, Foreign Key if any, Data types, Width, Domain and Integrity Constraints )

Identify the following

1) Identify the type of relationship (1:1, 1:m, m:m)

2) Identify the cardinality (Min, Max)

3) Primary Key

EER diagram 1):

①Employee → Mechanic 1:m

②Employee → Salesman 1:m

③Mechanic → RepairJob 1:m

④RepairJob → Cars m:1

⑤ Salesman → Clients 1:m

⑥ Salesman → Cars 1:m

①Mechanic does RepairJob is (0,m)

②RepairJob repairs is (1,m)

③Cars is repaired by RepairJob is (0,1)

④Salesman buys is (0,m)

⑤Clients buys is (0,m)

⑥Clients sells is (0,m)

⑦Client (0,m)

The underlined one is primary key

①Mechanic (MNumber,MName)

②RepairJob (Description, RepairNumber,MNumber,License,Cost)

③RepairCost (RepairNumber,MNumber,License,Parts,Work)

④Car (License,Manufacturer,Model,Year);

⑤Salesman (SNumber,SName);

⑥Client (CID,CName,Address);

⑦ClicentPhone (CID,Phone);

⑧Buys(SNumber,CID,License,Price,Date,Value);

⑨Sells(SNumber,CID,License,Date,Value,Commission);

I use the following MySQL code to establish these tables:

CREATE TABLE `Mechanic` (
    `MNumber` INT(4) NOT NULL PRIMARY KEY,
    `Name` VARCHAR(20) NOT NULL
);
CREATE TABLE `Salesman` (
    `SNumber` INT(4) NOT NULL PRIMARY KEY,
    `Name` VARCHAR(20) NOT NULL
);
CREATE TABLE `RepairJob` (
    `RepairNumber` INT(4) NOT NULL PRIMARY KEY,
    `MNumber` INT(4),
    FOREIGN KEY (MNumber)
        REFERENCES Mechanic (MNumber),
    `License` VARCHAR(20),
    FOREIGN KEY (License)
        REFERENCES car (License),
    `Description` VARCHAR(20) NOT NULL,
    `Parts` VARCHAR(20) NOT NULL,
    `Work` VARCHAR(20) NOT NULL
);
CREATE TABLE `Client` (
    `ID` INT(4) NOT NULL PRIMARY KEY,
    `Phone` INT(11) NOT NULL,
    `Address` VARCHAR(20) NOT NULL,
    `Name` VARCHAR(20) NOT NULL
);
CREATE TABLE `Phone` (
    `ID` INT(4),
    FOREIGN KEY (ID)
        REFERENCES Client (ID),
    `Phone` INT(11) NOT NULL
);
CREATE TABLE `Car` (
    `License` VARCHAR(20) NOT NULL PRIMARY KEY,
    `SNumber` INT(4),
    FOREIGN KEY (SNumber)
        REFERENCES Salesman (SNumber),
    `ID` INT(4),
    FOREIGN KEY (ID)
        REFERENCES Client (ID),
    `Manufacturer` VARCHAR(10) NOT NULL,
    `Model` VARCHAR(10) NOT NULL,
    `Year` DATE NOT NULL
);
CREATE TABLE `Buys` (
    `SNumber` INT(4),
    FOREIGN KEY (SNumber)
        REFERENCES Salesman (SNumber),
    `License` VARCHAR(20) NOT NULL,
    FOREIGN KEY (License)
        REFERENCES Car (License),
    `ID` INT(4) NOT NULL,
    FOREIGN KEY (ID)
        REFERENCES Client (ID),
    `Price` INT(10) NOT NULL,
    `Date` DATE NOT NULL,
    `Value` INT(10) NOT NULL
);
CREATE TABLE `Buys` (
    `SNumber` INT(4),
    FOREIGN KEY (SNumber)
        REFERENCES Salesman (SNumber),
    `License` VARCHAR(20) NOT NULL,
    FOREIGN KEY (License)
        REFERENCES Car (License),
    `ID` INT(4) NOT NULL,
    FOREIGN KEY (ID)
        REFERENCES Client (ID),
    `Commission` INT(10) NOT NULL,
    `Date` DATE NOT NULL,
    `Value` INT(10) NOT NULL
);

Case Study

A relational database is to be designed for a medium sized Company dealing with developing application software. The Company delivers various products to its customers ranging from a single application program to customized software. The Company employs various experts, consultants and supporting staff. All personnel are employed on long-term basis, i.e. there are no short-term or temporary staffs.

Although the Company is somehow structured for administrative purposes (that is, it is divided into departments headed by department managers) all projects are carried out in an inter-disciplinary way. For each project has a Start date when a project team is selected, grouping employees from different departments, and a Project Manager (also an employee of the Company) is appointed who is entirely and exclusively responsible for the control of the project, quite independently of the Company’s hierarchy. The following is a brief statement of some facts and policies adopted by the Company.

Entities

• Department

• Employee

• Skills

• Team

• Customers

• Product

• Projects

• Spouse

• Dependent(children of employee)

Relationships

• Employees work for different department headed by a Manager

• Employee has Spouse and Dependent (child)

• Each department manages many employees.

• Employees are divided into many teams

• Each Team manages a project

• Each Department is Headed by a Department Manager

• Each Employees belongs to one team

• A Project is headed by Project Team

• Skills are used in projects

Draw the Logical Data Model and Physical Data Model for Oracle DBMS using Power Designer

Logical Data Model

Physical Data Model: