Question

2. The following figure shows an ER schema for a university database. a) Map this schema into a relational schema b)Show the SQL statements needed to create the relations, using proper constraints. If your translation cannot capture any constraints in the ER diagram, explain why. COffice CName CPhone COLLEGE 0. 1 D Rank Office IName IPhone INSTRUCTOR D EAN (0.NO CStartDate (0.1) <ADMINS . CHIAR (1,1) EMPLOYS (MName FName LName (SName DOB DName Sid IO.NO Name Poate DEPT on H AS 01 DOBS STUDENT ON Add Phong Major DOffice DPhone (ON (ON) Grade OFFERS TAKES TEACHES (5.1) (1,1) (1.1) Secid) SecNo SECTION (ON CCode COURSE Credits CoName CDesc Level Days Time Year Sem CRoom Bldg RoomNo

Answer 1

If you have any doubts, please give me comment...

COLLEGE(CName, COffice, Cphone, deanId);

FOREIGN KEY deanID REFERENCES INSTRUCTOR(id)

DEPT(Dcode, DName, DOffice, Dphone, CName, ChairID, CStartDate);

FOREIGN KEY CName REFERENCES COLLEGE(CName);

FOREIGN KEY ChairID REFERENCES INSTRUCTOR(id);

INSTRUCTOR(id, Rank, IName, IOffice, IPhone, DCode);

FOREIGN KEY DCode REFERENCES DEPT(DCode)

COURSE(CCode, Credits, CoName, Level, CDesc, DCode);

FOREIGN KEY DCode REFERENCES DEPT(DCode)

SECTION(SecId, SecNo, Sem, Year, CRoomBldg, CRoomRoomNo, DaysInTime, CCode, InstrId);

FOREIGN KEY InstID REFERENCES INSTRUCTOR(id);

FOREIGN KEY CCode REFERENCES COURSE(CCode);

STUDENT(SId, FName, MName, LName, DOB, Addr, Phone, Major, DCode);

FOREIGN KEY DCode REFERENCES DEPT(DCode)

TAKES(SId, SecId, Grade);

FOREIGN KEY SId REFERENCES STUDENT(SId)

FOREIGN KEY SecId REFERENCES SECTION(SecId)

SQL:

CREATE TABLE INSTRUCTOR(

    id INT NOT NULL PRIMARY KEY,

    Rank VARCHAR(10),

    IName VARCHAR(50),

    IOffice VARCHAR(50),

    IPhone VARHCAR(15),

    DCode CHAR(5)

);

CREATE TABLE COLLEGE(

    CName VARCHAR(50),

    COffice VARCHAR(50),

    Cphone VARCHAR(15),

    deanId INT,

    FOREIGN KEY(deanID) REFERENCES INSTRUCTOR(id)

);

CREATE TABLE DEPT(

    Dcode VARCHAR(5) NOT NULL PRIMARY KEY,

    DName VARCHAR(50) UNIQUE,

    DOffice VARCHAR(50),

    Dphone VARCHAR(15),

    CName VARCHAR(50),

    ChairID INT,

    CStartDate DATE,

    FOREIGN KEY (CName) REFERENCES COLLEGE(CName),

    FOREIGN KEY (ChairID) REFERENCES INSTRUCTOR(id)

);

CREATE TABLE COURSE(

    CCode CHAR(6) NOT NULL PRIMARY KEY,

    Credits INT,

    CoName VARCHAR(50) UNIQUE,

    Level INT,

    CDesc VARCHAR(100),

    DCode CHAR(5),

    FOREIGN KEY(DCode) REFERENCES DEPT(DCode)

);

CREATE TABLE SECTION(

    SecId INT NOT NULL PRIMARY KEY,

    SecNo INT,

    Sem VARHCAR(10),

    Year INT,

    CRoomBldg VARHCAR(20),

    CRoomRoomNo VARCHAR(20),

    DaysInTime VARCHAR(50),

    CCode CHAR(6),

    InstrId INT,

    FOREIGN KEY(InstID) REFERENCES INSTRUCTOR(id),

    FOREIGN KEY(CCode) REFERENCES COURSE(CCode)

);

CREATE TABLE STUDENT(

    SId INT NOT NULL PRIMARY KEY,

    FName VARCHAR(50),

    MName VARCHAR(5),

    LName VARCHAR(50),

    DOB DATE,

    Addr VARCHAR(50),

    Phone VARCHAR(15),

    Major VARCHAR(10),

    DCode VARCHAR(5),

    FOREIGN KEY (DCode) REFERENCES DEPT(DCode)

);

CREATE TABLE TAKES(

    SId INT,

    SecId INT,

    Grade CHAR(1),

    FOREIGN KEY (SId) REFERENCES STUDENT(SId),

    FOREIGN KEY (SecId) REFERENCES SECTION(SecId)

);

ALTER TABLE INSTRUCTOR ADD FOREIGN KEY(DCode) REFERENCES DEPT(DCode);

Let me know if any errors raised, Thank you...