a. Implement MC5 ExoProtect Employees’ Computers Database depicted by the relati

Question

a. Implement MC5 ExoProtect Employees’ Computers Database depicted by the relational schema that you created in Database Project Part 2.

i. Write CREATE TABLE statements to create the tables.

ii. Use INSERT INTO statements to insert no fewer than 2 and no more than 10 records per table in this database.

Note: You must use the information about real-world computers in the table. You can go to computer vendor websites, like dell.com, to find such information.

b. Implement MC6 Jones Sozers Sales and Rentals Database by the relational schema that you created in Database Project Part 2.

i. Write CREATE TABLE statements to create the tables.

ii. Use INSERT INTO statements to insert no fewer than 2 and no more than 10 records per table in this database.

Note: You must use the information about real-world construction equipment. You can go to the websites of construction equipment rental companies to find such information. One example is http://www.neffrental.com/equipment/rental/.

Requirements: • Your CREATE TABLE, INSERT statements must be listed in the order that is allowed by foreign key constraints. It's recommended that you run those statements in SQL Developer to make sure that the order is correct. • You must type everything in a computer file. • Your CREATE TABLE, INSERT statements must work in Oracle. • Remember to define necessary constraints.

CertifiedToUse SOFTWARE PACKAGE SPName SPID (FK) SPID (FK) EmplD (FK EMPLOYEE SKILL EmpSkill EmplD (FK InstalledAt ComplD (FK) EMPLOYEE EmplD EmpName CompID (FK) COMPUTER ComplD CompModel CompMake MC5 ExoProtect

Explanation / Answer

a) CREATE TABLE Statements:-

CREATE TABLE SOFTWARE_PACKAGE (

SPID VARCHAR2(10),

SPNAME VARCHAR2(100) NOT NULL,

CONSTRAINT SOFTWARE_PACKAGE_PK PRIMARY KEY (SPID));

CREATE TABLE COMPUTER (

COMPID VARCHAR2(10),

COMPMODEL VARCHAR2(100),

COMPMAKE VARCHAR2(100),

CONSTRAINT COMPUTER_PK PRIMARY KEY (COMPID));

CREATE TABLE INSTALLED_AT (

SPID VARCHAR2(10),

COMPID VARCHAR2(10),

CONSTRAINT INSTALLED_AT_FK1 FOREIGN KEY (SPID) REFERENCES SOFTWARE_PACKAGE(SPID),

CONSTRAINT INSTALLED_AT_FK2 FOREIGN KEY (COMPID) REFERENCES COMPUTER(COMPID));

CREATE TABLE EMPLOYEE (

EMPID VARCHAR2(10),

EMPNAME VARCHAR2(100) NOT NULL,

COMPID VARCHAR2(10),

CONSTRAINT EMPLOYEE_PK PRIMARY KEY (EMPID),

CONSTRAINT EMPLOYEE_FK FOREIGN KEY (COMPID) REFERENCES COMPUTER(COMPID));

CREATE TABLE CERTIFIED_TO_USE(

SPID VARCHAR2(10),

EMPID VARCHAR2(10),

CONSTRAINT CERTIFIED_TO_USE_FK1 FOREIGN KEY (SPID) REFERENCES SOFTWARE_PACKAGE(SPID),

CONSTRAINT CERTIFIED_TO_USE_FK2 FOREIGN KEY (EMPID) REFERENCES EMPLOYEE(EMPID));

CREATE TABLE EMPLOYEE_SKILL (

EMPSKILL NOT NULL,

EMPID VARCHAR2(10),

CONSTRAINT EMPLOYEE_SKILL_FK FOREIGN KEY (EMPID) REFERENCES EMPLOYEE(EMPID));

B) INSERT INTO SOFTWARE_PACKAGE VALUES ('ORA-001','Oracle Database 11g Server');

INSERT INTO SOFTWARE_PACKAGE VALUES ('ORA-002','Oracle E-Business Suit');

INSERT INTO COMPUTER VALUES ('COMP-001','HP-PAVILION 15.6','HP');

INSERT INTO COMPUTER VALUES ('COMP-002','DELL INSPIRION','DELL');

INSERT INTO INSTALLED_AT VALUES ('ORA-002', 'COMP-001');

  INSERT INTO INSTALLED_AT VALUES ('ORA-002', 'COMP-002');

INSERT INTO EMPLOYEE VALUES ('435678','GOPAL MALAKER','COMP-001');

  INSERT INTO EMPLOYEE VALUES ('11223344','JACK SPARROW','COMP-002');

INSERT INTO CERTIFIED_TO_USE VALUES ('ORA-001', '435678');

  INSERT INTO CERTIFIED_TO_USE VALUES ('ORA-001', '11223344');

INSERT INTO EMPLOYEE_SKILL VALUES ('PROGRAMMING', '11223344');

  INSERT INTO EMPLOYEE_SKILL VALUES ('SWIMMING', '11223344');

COMMIT;

b) CREATE TABLE Statements:-

CREATE TABLE EQUIPMENT_DETAIL (

EQUIPDETAILID VARCHAR2(10),

MAKE VARCHAR2(100),

TYPE VARCHAR2(100),

MODEL VARCHAR2(100),

CONSTRAINT EQUIPMENT_DETAIL_PK PRIMARY KEY (EQUIPDETAILID));

CREATE TABLE CUSTOMER(

CUSTID VARCHAR2(10),

CUSTNAME VARCHAR2(100),

CUSTCATEGORY VARCHAR2(100),

CONSTRAINT CUSTOMER_PK PRIMARY KEY (CUSTID));

CREATE TABLE EQUIPMENT (

SERIAL_NO VARCHAR2(10),

LAST_INSPECTION_DATE DATE,

DATEMADE DATE,

EQUIPDETAILID VARCHAR2(10),

CONSTRAINT EQUIPMENT_PK PRIMARY KEY(SERIAL_NO),

CONSTRAINT EQUIPMENT_FK FOREIGN KEY (EQUIPDETAILID) REFERENCES EQUIPMENT_DETAIL(EQUIPDETAILID));

CREATE TABLE SALES_REP(

SREPID VARCHAR2(10),

RANK VARCHAR2(10),

SREPLNAME VARCHAR2(100) NOT NULL,

SREPFNAME VARCHAR2(100),

MENTOR_SREPID VARCHAR2(10),

CONSTRAINT SALES_REP_PK PRIMARY KEY (SREPID ));

CREATE TABLE RENTAL (

RENTTRANSID VARCHAR2(10),

RENT_DATE DATE,

PRICE NUMBER,

SERIAL_NO VARCHAR2(10),

CUSTID VARCHAR2(10),

SREPID VARCHAR2(10),

CONSTRAINT RENTAL_PK PRIMARY KEY (RENTTRANSID),

CONSTRAINT RENTAL_FK1 FOREIGN KEY (SERIAL_NO) REFERENCES EQUIPMENT(SERIAL_NO),

CONSTRAINT RENTAL_FK2 FOREIGN KEY (CUSTID) REFERENCES CUSTOMER(CUSTID),

CONSTRAINT RENTAL_FK3 FOREIGN KEY (SREPID) REFERENCES SALES_REP (SREPID));

CREATE TABLE SALE (

SALETRANSID VARCHAR2(10),

PRICE NUMBER,

SALE_DATE DATE,

CUSTID VARCHAR2(10),

SERIAL_NO VARCHAR2(10),

SREPID VARCHAR2(10),

CONSTRAINT SALE _PK PRIMARY KEY (SALETRANSID),

CONSTRAINT SALE_FK1 FOREIGN KEY (CUSTID) REFERENCES CUSTOMER(CUSTID),

CONSTRAINT SALE_FK2 FOREIGN KEY (SERIAL_NO) REFERENCES EQUIPMENT(SERIAL_NO),

CONSTRAINT SALE_FK3 FOREIGN KEY (SREPID) REFERENCES SALES_REP(SREPID));

Please let me know in case of any clarifications required. Thanks!

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

---

---

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