SIGNMENT_5 Consider the following relational schema, referential integrity const

Question

SIGNMENT_5

Consider the following relational schema, referential integrity constraints, and the database state. Write SQL DDL statements to define the database and DML statements to populate the database. Make any other reasonable assumptions (e.g. for domains/data-types).

Primary Keys:

STUDENT – Student_number

COURSE – Course_number

PREREQUISITE - Course_number, Prerequisite_number

SECTION – Section_identifier

GRADE_REPORT - Student_number, Section_identifier

The following referential integrity constraints should hold (we use the notation:

R.(A1, ..., An) --> S.(B1, ..., Bn)

to represent a foreign key from the attributes A1, ..., An of R (the referencing relation)

to S (the referenced relation)):

PREREQUISITE.(CourseNumber) --> COURSE.(CourseNumber)

PREREQUISITE.(PrerequisiteNumber) --> COURSE.(CourseNumber)

SECTION.(CourseNumber) --> COURSE.(CourseNumber)

GRADE_REPORT.(StudentNumber) --> STUDENT.(StudentNumber)

GRADE_REPORT.(SectionIdentifier) --> SECTION.(SectionIdentifier)

Specify the following queries in SQL on the above database schema.

Retrieve the names of all senior students majoring in ‘CS’ (computer science).

Retrieve the names of all courses taught by Professor King in 2007 and 2008.

For each section taught by Professor King, retrieve the course number, semester, year, and number of students who took the section.

Retrieve the name and transcript of each senior student (Class = 4) majoring in CS.A transcript includes course name, course number, credit hours, semester, year, and grade for each course completed by the student.

Show each step and all work in Oracle SQL developer

    
Questions (i.e. create scheme statements (tables and constraints), insert statements, and queries with results) from Oracle SQL Developer (or similar), paste in a word document

Explanation / Answer

DCL

DML

Table 1

Insert into student values (‘Srith’,17,1,’CS’)

                                Table 2

                                                Insert into course values(‘Data Structure’,’CS12345’,4,’CS’)

                                Table 3

                                                Insert into section values(85,’ CS12345’,’FULL’,07,’King’);

                                Table 4

                                                Insert into grade_report values (17,112,’B’)

                                Table 5

                                                Insert into prerequisite (‘CS12345’’ C8888120’)

query

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

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