【系统设计与数据库系统】Data Modelling

github

Data Modelling

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 )

EER diagram 1):

Identify the following

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

1:1
Employee:Mechanic
Employee:Salesman

1:m
Car:RepairJob
Salesman:Cars
Client:Cars

M:m
Mechanic:RepairJob
Salesman:Client

2) Identify the cardinality (Min, Max)

3) Primary Key

Table Employee: Number
Table Repair: Number
Table Cars: License
Table Client: ID
Table Buys: SalesmanNumber, License, ID
Table Sells: SalesmanNumber, License, ID
Table Employee: Number
Table Mechanic: MechanicNumber
Table Salesman: SalesmanNumber

SQL：

-- -----------------------------------------------------
-- Schema CarSales
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `CarSales` DEFAULT CHARACTER SET utf8 ;
USE `CarSales` ;

-- -----------------------------------------------------
-- Table `CarSales`.`Employee`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `CarSales`.`Employee` (
  `Number` INT NOT NULL,
  `Name` VARCHAR(50) NULL,
  PRIMARY KEY (`Number`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `CarSales`.`Mechanic`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `CarSales`.`Mechanic` (
  `MechanicNumber` INT NOT NULL,
  PRIMARY KEY (`MechanicNumber`),
  CONSTRAINT `fk_Mechanic_Employee`
    FOREIGN KEY (`MechanicNumber`)
    REFERENCES `CarSales`.`Employee` (`Number`))
;


-- -----------------------------------------------------
-- Table `CarSales`.`Salesman`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `CarSales`.`Salesman` (
  `SalesmanNumber` INT NOT NULL,
  PRIMARY KEY (`SalesmanNumber`),
  CONSTRAINT `fk_Salesman_Employee1`
    FOREIGN KEY (`SalesmanNumber`)
    REFERENCES `CarSales`.`Employee` (`Number`));


-- -----------------------------------------------------
-- -----------------------------------------------------
-- Table `CarSales`.`Cars`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `CarSales`.`Cars` (
  `License` VARCHAR(45) NOT NULL,
  `Model` VARCHAR(45) NULL,
  `Manufacturer` VARCHAR(45) NULL,
  `Year` YEAR(4) NULL,
  PRIMARY KEY (`License`));


-- -----------------------------------------------------
-- Table `CarSales`.`RepairJob`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `CarSales`.`RepairJob` (
  `Number` INT NOT NULL,
  `MechanicNumber` INT NULL,
  `License` VARCHAR(45) NULL,
  `Description` VARCHAR(100) NULL,
  `PartsCost` FLOAT NULL,
  `WorkCost` FLOAT NULL,
  PRIMARY KEY (`Number`),
  INDEX `fk_RepairJob_Mechanic1_idx` (`MechanicNumber` ASC) VISIBLE,
  INDEX `fk_RepairJob_Cars1_idx` (`License` ASC) VISIBLE,
  CONSTRAINT `fk_RepairJob_Mechanic1`
    FOREIGN KEY (`MechanicNumber`)
    REFERENCES `CarSales`.`Mechanic` (`MechanicNumber`),
  CONSTRAINT `fk_RepairJob_Cars1`
    FOREIGN KEY (`License`)
    REFERENCES `CarSales`.`Cars` (`License`));


-- -----------------------------------------------------
-- Table `CarSales`.`Client`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `CarSales`.`Client` (
  `ID` INT NOT NULL,
  `Name` VARCHAR(45) NULL,
  `Address` VARCHAR(100) NULL,
  `Phone` INT NULL,
  PRIMARY KEY (`ID`));

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

1) Identify all entities you can think of

• Department
• Employee
• Skills
• Team
• Customers
• Product
• Projects
• Spouse
• Dependent(children of employee)

2) Identify all the attributes

• TABLE customers (CustomerID, FirstName, LastName ,Phone, Email, StreetAddress,City,State)
• TABLE department (DepartmentID, DepartmentName,ManagerID)
• TABLE dependent (EmployeeID, DependentName)
• TABLE employee (EmployeeID, EmpName, JobTitle, DepartmentID, TeamID, DOB, Phone,Address)
• TABLE employeeskill ( EmployeeID, SkillID, SkillName)
• TABLE orders (OrderID, ProductID, CustomerID, OrderDate, ShipDate, UnitPrice, Quantity, SalespersonID)
• TABLE product (ProductID, ProductName, ProductType)
• TABLE project (ProjectID, TeamID, StartDate, EndDate, ProjectManagerID)
• TABLE projectskill (ProjectID, SkillID)
• TABLE skills (SkillID, SkillName)
• TABLE spouse (EmployeeID, SpouseName)
• TABLE team (TeamID, TeamName)

3) Identify atomic, composite and derived attributes

• atomic
  CustomerID,Phone,Email,DepartmentID,DepartmentID,EmployeeID,EmpName, JobTitle,SkillID,SkillName,OrderID,ShipDate,UnitPrice,Quantity,ProductID,ProductName, ProductType, TeamID,TeamName,SpouseName
• composite
  CustomerName,Address,
• derived
  Order

4) Identify Primary key or Composite Primary Key

• TABLE customers (CustomerID, FirstName, LastName ,Phone, Email, StreetAddress,City,State)
• TABLE department (DepartmentID, DepartmentName,ManagerID)
• TABLE dependent (EmployeeID, DependentName)
• TABLE employee (EmployeeID, EmpName, JobTitle, DepartmentID, TeamID, DOB, Phone,Address)
• TABLE employeeskill ( EmployeeID, SkillID, SkillName)
• TABLE orders (OrderID, ProductID, CustomerID, OrderDate, ShipDate, UnitPrice, Quantity, SalespersonID)
• TABLE product (ProductID, ProductName, ProductType)
• TABLE project (ProjectID, TeamID, StartDate, EndDate, ProjectManagerID)
• TABLE projectskill (ProjectID, SkillID)
• TABLE skills (SkillID, SkillName)
• TABLE spouse (EmployeeID, SpouseName)
• TABLE team (TeamID, TeamName)

5) Identify all relationships between the entities

• 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

6) Identify any attributes for relationship

No

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

PDM：

SQL：

-- -----------------------------------------------------
-- Schema CaseStudy
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `CaseStudy` DEFAULT CHARACTER SET utf8 ;
USE `CaseStudy` ;

-- -----------------------------------------------------
-- Table `CaseStudy`.`Department`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `CaseStudy`.`Department` (
  `DepartmentID` INT NOT NULL,
  `DepartmentName` VARCHAR(45) NULL,
  `ManagerID` INT NULL,
  PRIMARY KEY (`DepartmentID`),
  INDEX `fk_Department_Employee_idx` (`ManagerID` ASC) VISIBLE,
  CONSTRAINT `fk_Department_Employee`
    FOREIGN KEY (`ManagerID`)
    REFERENCES `CaseStudy`.`Employee` (`EmployeeID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `CaseStudy`.`Team`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `CaseStudy`.`Team` (
  `TeamID` INT NOT NULL,
  `TeamName` VARCHAR(45) NULL,
  PRIMARY KEY (`TeamID`))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `CaseStudy`.`Employee`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `CaseStudy`.`Employee` (
  `EmployeeID` INT NOT NULL,
  `EmpName` VARCHAR(45) NULL,
  `JobTitle` VARCHAR(45) NULL,
  `DepartmentID` INT NULL,
  `TeamID` INT NULL,
  `DOB` DATE NULL,
  `Phone` INT NULL,
  `Address` VARCHAR(100) NULL,
  PRIMARY KEY (`EmployeeID`),
  INDEX `fk_Employee_Department1_idx` (`DepartmentID` ASC) VISIBLE,
  INDEX `fk_Employee_Team1_idx` (`TeamID` ASC) VISIBLE,
  CONSTRAINT `fk_Employee_Department1`
    FOREIGN KEY (`DepartmentID`)
    REFERENCES `CaseStudy`.`Department` (`DepartmentID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Employee_Team1`
    FOREIGN KEY (`TeamID`)
    REFERENCES `CaseStudy`.`Team` (`TeamID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `CaseStudy`.`Product`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `CaseStudy`.`Product` (
  `ProductID` INT NOT NULL,
  `ProductName` VARCHAR(50) NULL,
  `ProductType` VARCHAR(45) NULL,
  PRIMARY KEY (`ProductID`))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `CaseStudy`.`Project`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `CaseStudy`.`Project` (
  `ProjectID` INT NOT NULL,
  `TeamID` INT NULL,
  `StartDate` DATE NULL,
  `EndDate` DATE NULL,
    `ProjectManagerID` INT NULL,
  PRIMARY KEY (`ProjectID`),
  INDEX `fk_Project_Team1_idx` (`TeamID` ASC) VISIBLE,
  INDEX `fk_Project_Employee1_idx` (`ProjectManagerID` ASC) VISIBLE,
  CONSTRAINT `fk_Project_Team1`
    FOREIGN KEY (`TeamID`)
    REFERENCES `CaseStudy`.`Team` (`TeamID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Project_Employee1`
    FOREIGN KEY (`ProjectManagerID`)
    REFERENCES `CaseStudy`.`Employee` (`EmployeeID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `CaseStudy`.`Dependent`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `CaseStudy`.`Dependent` (
  `EmployeeID` INT NOT NULL,
  `DependentName` VARCHAR(45) NULL,
  PRIMARY KEY (`EmployeeID`),
  CONSTRAINT `fk_Dependent_Employee1`
    FOREIGN KEY (`EmployeeID`)
    REFERENCES `CaseStudy`.`Employee` (`EmployeeID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `CaseStudy`.`Skills`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `CaseStudy`.`Skills` (
  `SkillID` INT NOT NULL,
  `SkillName` VARCHAR(45) NULL,
  PRIMARY KEY (`SkillID`))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `CaseStudy`.`EmployeeSkill`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `CaseStudy`.`EmployeeSkill` (
  `EmployeeID` INT NOT NULL,
  `SkillID` INT NOT NULL,
  `Level` INT NULL,
    PRIMARY KEY (`EmployeeID`, `SkillID`),
  INDEX `fk_EmployeeSkill_Skills1_idx` (`SkillID` ASC) VISIBLE,
  CONSTRAINT `fk_EmployeeSkill_Employee1`
    FOREIGN KEY (`EmployeeID`)
    REFERENCES `CaseStudy`.`Employee` (`EmployeeID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_EmployeeSkill_Skills1`
    FOREIGN KEY (`SkillID`)
    REFERENCES `CaseStudy`.`Skills` (`SkillID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `CaseStudy`.`EmployeeSkill_copy1`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `CaseStudy`.`EmployeeSkill_copy1` (
  `EmployeeID` INT NOT NULL,
  `SkillID` INT NOT NULL,
  `Level` INT NULL,
  PRIMARY KEY (`EmployeeID`, `SkillID`),
  INDEX `fk_EmployeeSkill_Skills1_idx` (`SkillID` ASC) VISIBLE,
  CONSTRAINT `fk_EmployeeSkill_Employee10`
    FOREIGN KEY (`EmployeeID`)
    REFERENCES `CaseStudy`.`Employee` (`EmployeeID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_EmployeeSkill_Skills10`
    FOREIGN KEY (`SkillID`)
    REFERENCES `CaseStudy`.`Skills` (`SkillID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `CaseStudy`.`ProjectSkill`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `CaseStudy`.`ProjectSkill` (
  `ProjectID` INT NOT NULL,
  `SkillID` INT NOT NULL,
  PRIMARY KEY (`ProjectID`, `SkillID`),
  INDEX `fk_ProjectSkill_Skills1_idx` (`SkillID` ASC) VISIBLE,
  CONSTRAINT `fk_ProjectSkill_Project1`
    FOREIGN KEY (`ProjectID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_ProjectSkill_Skills1`
    FOREIGN KEY (`SkillID`)
    REFERENCES `CaseStudy`.`Skills` (`SkillID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `CaseStudy`.`Customers`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `CaseStudy`.`Customers` (
  `CustomerID` INT NOT NULL,
  `FirstName` VARCHAR(45) NULL,
  `LastName` VARCHAR(45) NULL,
  `Phone` INT NULL,
  `Email` VARCHAR(45) NULL,
  `StreetAddress` VARCHAR(100) NULL,
  `City` VARCHAR(45) NULL,
  `State` VARCHAR(45) NULL,
  PRIMARY KEY (`CustomerID`))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `CaseStudy`.`Spouse`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `CaseStudy`.`Spouse` (
  `EmployeeID` INT NOT NULL,
  `SpouseName` VARCHAR(45) NULL,
  PRIMARY KEY (`EmployeeID`),
  CONSTRAINT `fk_Spouse_Employee1`
    FOREIGN KEY (`EmployeeID`)
    REFERENCES `CaseStudy`.`Employee` (`EmployeeID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `CaseStudy`.`Orders`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `CaseStudy`.`Orders` (
  `OrderID` INT NOT NULL,
  `ProductID` INT NULL,
  `CustomerID` INT NULL,
  `OrderDate` DATE NULL,
  `ShipDate` DATE NULL,
  `UnitPrice` FLOAT NULL,
  `Quantity` INT NULL,
  `SalespersonID` INT NULL,
  PRIMARY KEY (`OrderID`),
  INDEX `fk_Orders_Customers1_idx` (`CustomerID` ASC) VISIBLE,
  INDEX `fk_Orders_Product1_idx` (`ProductID` ASC) VISIBLE,
  INDEX `fk_Orders_Employee1_idx` (`SalespersonID` ASC) VISIBLE,
  CONSTRAINT `fk_Orders_Customers1`
    FOREIGN KEY (`CustomerID`)
    REFERENCES `CaseStudy`.`Customers` (`CustomerID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Orders_Product1`
    FOREIGN KEY (`ProductID`)
    REFERENCES `CaseStudy`.`Product` (`ProductID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Orders_Employee1`
    FOREIGN KEY (`SalespersonID`)
    REFERENCES `CaseStudy`.`Employee` (`EmployeeID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;