Question

Write create table statements to create tables for the Exoproduct employees computers database depicted by the relational schema created in a mini case MC5 in chapter 3. Use insert into statements to insert no fewer than 2 and no more than 10 records per table.

Answer 1

In case of any queries,please comment. I would be very happy to assist all your queries.Please give a Thumps up if you like the answer.

The create table statements are

CREATE TABLE SOFTWARE_PACKAGE ( SPID VARCHAR(10), SPNAME VARCHAR(100) NOT NULL, CONSTRAINT SOFTWARE_PACKAGE_PK PRIMARY KEY (SPID));
CREATE TABLE COMPUTER ( COMPID VARCHAR(10), COMPMODEL VARCHAR(100), COMPMAKE VARCHAR(100),CONSTRAINT COMPUTER_PK PRIMARY KEY (COMPID));
CREATE TABLE EMPLOYEE (EMPID VARCHAR(10), EMPNAME VARCHAR(100) NOT NULL, COMPID VARCHAR(10) ,CONSTRAINT EMPLOYEE_PK PRIMARY KEY (EMPID),CONSTRAINT EMPLOYEE_FK FOREIGN KEY (COMPID) REFERENCES COMPUTER(COMPID));

CREATE TABLE EMPLOYEE_SKILL(EMPSKILL VARCHAR(100), EMPID VARCHAR(10) , CONSTRAINT EMP_SKILL_PK PRIMARY KEY (EMPSKILL,EMPID),CONSTRAINT EMPLOYEE_SKILL_FK FOREIGN KEY (EMPID) REFERENCES EMPLOYEE(EMPID));

CREATE TABLE INSTALLEDAT(SPID VARCHAR(10), COMPID VARCHAR(10) , CONSTRAINT INSTALLEDAT_PK PRIMARY KEY (COMPID, SPID), CONSTRAINT INSTALLEDAT_FK1 FOREIGN KEY (SPID) REFERENCES SOFTWARE_PACKAGE(SPID),CONSTRAINT INSTALLEDAT_FK2 FOREIGN KEY (COMPID) REFERENCES COMPUTER(COMPID));

CREATE TABLE CERTIFIEDTOUSE( EMPID VARCHAR(10) , SPID VARCHAR(10),CONSTRAINT CERTIFIEDTOUSE_PK PRIMARY KEY (EMPID, SPID), CONSTRAINT CERTIFIEDTOUSE_FK2 FOREIGN KEY (EMPID) REFERENCES EMPLOYEE(EMPID),CONSTRAINT CERTIFIEDTOUSE_FK1 FOREIGN KEY (SPID) REFERENCES SOFTWARE_PACKAGE(SPID));

The insert into statements are

#software package table
insert into SOFTWARE_PACKAGE values('1','QuickNumbers');
insert into SOFTWARE_PACKAGE values('2','Presenter');
insert into SOFTWARE_PACKAGE values('3','WordSmith');
insert into SOFTWARE_PACKAGE values('4','GraphixStudio');

#COMPUTER table
insert into COMPUTER values('100','X200','Zenovo',);
insert into COMPUTER values ('101','X200','Zenovo');
insert into COMPUTER values('102','Bartlett','Pear');
insert into COMPUTER values('103','SlimBook','TamTung');
insert into COMPUTER values('104','X500','Zenovo');

#employee table
insert into EMPLOYEE values('30','Jolly',100);
insert into EMPLOYEE values('31','James',101);
insert into EMPLOYEE values('32','Eric',102);
insert into EMPLOYEE values('33','Christiano',103);

#employee skills table
insert into EMPLOYEE_SKILL values('Back-End','30' );
insert into EMPLOYEE_SKILL values('Front-End','30' );
insert into EMPLOYEE_SKILL values('Back-End','31');
insert into EMPLOYEE_SKILL values('PC Maintenance','31');
insert into EMPLOYEE_SKILL values('Front-End','32');

#installedat table
insert into INSTALLEDAT values('1',101);
insert into INSTALLEDAT values('3',101);
insert into INSTALLEDAT values('2',102);
insert into INSTALLEDAT values('1',103);
insert into INSTALLEDAT values('3',104);

#certifiedtouse table
insert into CERTIFIEDTOUSE values('30','1');
insert into CERTIFIEDTOUSE values('30','3');
insert into CERTIFIEDTOUSE values('30','2');
insert into CERTIFIEDTOUSE values('31','1');
insert into CERTIFIEDTOUSE values('31','3');

The select statements are
#Display values
select * from SOFTWARE_PACKAGE;
select * from COMPUTER;
select * from EMPLOYEE;
select * from EMPLOYEE_SKILL;
select * from INSTALLEDAT;
select * from CERTIFIEDTOUSE;

Output