Create tables for the following relation schemas. STUDENT(Name, studentNo, major

Question

Create tables for the following relation schemas.

STUDENT(Name, studentNo, major) GRADEREPORT(StudentNo, sectionID, Grade)

The required data type of each attribute is described as below:

STUDENT.name is a variable length character string with no more than 40 characters

STUDENT.studentNo is a10-digitnumber, so is GRADEREPORT.StudentNo. (number(10))

STUDENT.major is a fixed-length string of 4 characters (char(4)).

GRADEREPORT.sectionID isa6-digitnumber(number(6)).

GRADEREPORT.Grade is a single letter (char(1)).

Your SQL program should specify each attribute type accordingly and appropriate constraints including primary keys and foreign keys, if any.

Tip: Run the drop table command first before the create table command. Here is an example:

Insert 3 rows to each table.
(a) Run the following codes to create EMPLOYEE and DEPARTMENT tables:

DNUMBER number(2), Dname varchar2(30), primary key (DNUMBER));

EID number(4) primary key,
DNO number(2),
AGE integer check(age>=18), foreignkey(DNO) references DEPT );

2. 3.

Explanation / Answer

1. (a).
drop table STUDENT cascade constraints;
create table STUDENT(
   studentNo number(10) primary key,
   name varchar2(40),
   major char(4)
);
  
drop table GRADEREPORT cascade constraints;
create table GRADEREPORT(
   studentNo number(10),
   sectionId number(6),
   grade char(1),
   foreignkey(studentNo) references STUDENT
);

1. (b).
INSERT INTO STUDENT (1,"Student 1","Maths");
INSERT INTO STUDENT (2,"Student 2","Arts");
INSERT INTO STUDENT (3,"Student 3","Economics");

INSERT INTO GRADEREPORT (1,534,"A");
INSERT INTO GRADEREPORT (2,343,"B");
INSERT INTO GRADEREPORT (3,341,"C");

2. (a)
drop table DEPT cascade constraints;
create table DEPT (
   DNUMBER number(2),
   Dname varchar2(30),
   primary key (DNUMBER)
);

drop table EMP cascade constraints;
create table EMP (
   EID number(4) primary key,
   DNO number(2),
   AGE integer check(age>=18),
   foreignkey(DNO) references DEPT
);

3. (a).
Insert into DEPT values (1, "SALES");
Insert into DEPT values (2, "MARKETING");
Insert into EMP values (1111, 1, 27);
Insert into EMP values (2222, 1, 54);
Insert into EMP values (3333, 2, 30);

3. (b).
SELECT * FROM EMP;

3. (c).
Insert into EMP values (2222, 1, 54);
Insert into EMP values (4444, 4, 54); // Failed because of foreign key constraint. Since 4 not present in DEPT table.
Insert into EMP values (5555, 1, 0); // Failed because of check constraint as AGE should be >=18

4. (a).
drop table DEPT cascade constraints;
create table DEPT (
   DNUMBER number(2),
   Dname varchar2(30)
);
drop table EMP cascade constraints;
create table EMP (
   EID number(4),
   DNO number(2),
   AGE integer
);

Insert into DEPT values (1, "SALES");
Insert into DEPT values (2, "MARKETING");
Insert into EMP values (1111, 1, 27);
Insert into EMP values (2222, 1, 54);
Insert into EMP values (3333, 2, 30);

4. (b).
SELECT * FROM EMP

Insert into EMP values (2222, 1, 54);
Insert into EMP values (4444, 4, 54);   //Executed successfully now because of no foreign key constraint
Insert into EMP values (5555, 1, 0);   //Executed successfully now because of no check constraint.

SELECT * FROM EMP

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

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