Database and Information Retrieval Assignment
Your client, University of Vermont, asks you to create a small database to keep the information of students, coursers, sections and the grades for each section of the students.
Refer the data tables (attached) and write answers or perform actions for following questions.
1. Identify primary keys, foreign keys, NULL and other constraints exist with the tables. Document the identified constraints. Use table structure 1 to document the constraints
|
Table 1. Identified Primary Key, Foreign key, Not null Constraints
|
|
Table Name
|
Primary Key
|
Foreign Key (reference table A)
|
Not Null column
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
2. Create all the tables with identified constraints on Oracle. (e.g. primary keys, foreign keys, NULL and other).
NOTE: When you create tables you should decide the appropriate types for the attributes.
3. Alter the student table and add new field Date of Birth for student table. Type for date of birth should be date.
4. Insert all the given values. You are feel free to insert new values to the tables. (Note - Select suitable values for Date of birth)
5. Write following queries on the tables.
a. List the course numbers, course names a student who is doing computer science major (e.g. department is CS) could enrol for.
b. Select and list the courses completed by Brown in 2008 Fall.
(Hint: if student is enrolled and not completed grade of the Grade_Report (TABLE) is marked as 'NC')
c. What are the prerequisites for course CS3380 (Database) ? List all the prerequisites if any.
d. For every course how many students have enrolled in the 2008 - Fall semester.
List the course number and the number of students. (Hint: if student is enrolled and not completed grade is marked as 'NC')
e. Display all the courses, their grades and credit hours for student Brown.
f. List the course number and the name offered during the 2008- Fall semester.
g. Find the students with age between 18 and 21. Print their student number, name and the age with the major.
h. Find all the instructors for Smith. List the course and the instructor name.
i. Create your own query. It must include a nested query. Submit the following:
i. question your query is answering
ii. the SQL query
iii. the mark for this question will depend on the complexity of the query.
iv. higher marks will be given for queries that are more complex and/or innovative.
v. if you do not provide a description of what question the query is answering, you will get zero for this query.
Attachment:- Assignment File.rar