How can I create this on MySQL Workbench? COLLEGE DEPARTMENT PROFESSOR Chairs/Ch

Question

How can I create this on MySQL Workbench?

COLLEGE DEPARTMENT PROFESSOR Chairs/Chaired ByProfessorFirstName CollegeName DepartmentName ProfessorLastName DeanFirstName DeanLastName Phone Building Room Phone TotalMajors Room CollegeName (FK) ProfessorFirstName (FK) (AK1.1) ProfessorLastName (FK) (AK1.2) Building OfficeNumber Phone APPOINTMENT Major DepartmentName (FK) ProfessorFirstName (FK) ProfessorLastName (FK) STUDENT StudentNumber Title Terms Title DepartmentName (FK) StudentFirstName StudentLastName HomeStreet HomeCity HomeState HomeZip Phone ProfessorDepartment (FK) ProfessorFirstName (FK) ProfessorLastName (FK) Adviser FIGURE 6-28 Database Design for Highline University

Explanation / Answer

When Came 1 to many relation Primary key of side of 1 entity will add to many side of entity. when it is added, that attribute called as foreign key.

CREATE TABLE COLLEGE(
    CollegeName VARCHAR(255) PRIMARY KEY,
    DeanFirstName VARCHAR(255),
    DeanLastName VARCHAR(255),
    Phone CHAR(10),
    Building VARCHAR(20),
    Room VARCHAR(10)
);

CREATE TABLE PROFESSOR(
    ProfessorFirstName VARCHAR(255),
    ProfessorLastName VARCHAR(255),
    Building VARCHAR(20),
    OfficeNumber VARCHAR(15),
    Phone VARCHAR(10),
    PRIMARY KEY(ProfessorFirstName, ProfessorLastName)
)

CREATE TABLE DEPARTMENT(
    DepartmentName VARCHAR(255) PRIMARY KEY,
    Phone CHAR(10),
    TotalMajors INT,
    Room VARCHAR(10),
    CollegeName VARCHAR(255),
    ProfessorFirstName VARCHAR(255),
    ProfessorLastName VARCHAR(255),
    FOREIGN KEY(CollegeName) REFERENCES COLLEGE(CollegeName),
    FOREIGN KEY(ProfessorFirstName, ProfessorLastName) REFERENCES PROFESSOR(ProfessorFirstName, ProfessorLastName)
)

CREATE TABLE APPOINTMENT(
    DepartmentName VARCHAR(255),
    ProfessorFirstName VARCHAR(255),
    ProfessorLastName VARCHAR(255),
    Title VARCHAR(50),
    Terms VARCHAR(255),
    PRIMARY KEY(DepartmentName, ProfessorFirstName, ProfessorLastName),
    FOREGIN KEY(DepartmentName) REFERENCES DEPARTMENT(DepartmentName),
    FOREGIN KEY(ProfessorFirstName, ProfessorLastName), REFERENCES PROFESSOR(ProfessorFirstName, ProfessorLastName)
)

CREATE TABLE STUDENT(
    StudentNumber CHAR(9) PRIMARY KEY,
    Title VARCHAR(100),
    DepartmentName VARCHAR(255),
    StudentFirstName VARCHAR(255),
    StudentLastName VARCHAR(255),
    HomeStreet VARCHAR(100),
    HomeCity VARCHAR(100),
    HomeState VARCHAR(100),
    HomeZip CHAR(6),
    Phone CHAR(10),
    ProfessorFirstName VARCHAR(255),
    ProfessorLastName VARCHAR(255),
    ProfessorDepartment VARCHAR(255)
    FOREIGN KEY(DepartmentName) REFERENCES DEPARTMENT(DepartmentName),
    FOREIGN KEY(ProfessorDepartment, ProfessorFirstName, ProfessorLastName) REFERENCES APPOINTMENT(DepartmentName, ProfessorFirstName, ProfessorLastName)
)

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.