CSC3400 | DATABASE SYSTEMS ASSIGN1 CSC850 | DATABASE

HI6006 Competitive Strategy
ITC516 | WEKA DATA MINING SOFTWARE INSTALLATION AND PROCESSING ASSESSMENT
March 7, 2018
HI6006 Competitive Strategy
COIT20245 | ASSIGNMENT 1 JAVA | JAVA
March 7, 2018

CSC3400 | DATABASE SYSTEMS ASSIGN1 CSC850 | DATABASE

HI6006 Competitive Strategy

CSC3400 | DATABASE SYSTEMS ASSIGN1 CSC850 | DATABASE

IT Assignment help

Question 1.                                      8 marks

Consider the following:

D1     =     fStudent1; Student2; Student3; Student4g

D2     =     f20183427g

D3     =     fundergraduate; postgraduateg

  • f(x; y; zj x 2 D1; y 2 D2; z 2 D3; and y   30g
  • Create a Relation Schema for S given above. Write out both the full and the abbreviated schemas as shown on Slide of the lecture notes in Module 3.
  • Using a table as a physical representation, create a relation for S as defined above. The table should include ALL the possible tuples for S and has column headings that correspond to the attributes of the relation.
  • What is the cardinality of S, the table you have constructed in Ques-tion 1(b)?
  • What is the degree of S?

Question 2.                                                            12 marks

 

Consider the following relational schema:

Student(stdNo, firstName, lastName, eMail)

Course(code, name, credits)

WhenOffered(course, semester, year, examiner)

Staff(staffID, name, birth-date, department)

Transcript(student, course, semes, year, grade)

Related image

This is a schema for a database maintained by a small college to keep track of students, courses, etc. Students have their own unique student ID (which may only contain numbers) and can be contacted via their own unique college email address. There are no two courses having the same code or name. A course is offered at most once per semester (where 1 semester 3), but can be offered in several semesters or in different years. A course offer is examined by a specific member of staff. The examiner can vary over different semesters, and can examine more than one course in a given semester. A staff belongs to one and only one department at the university. A student enrolled in a specific course offer obtains a grade for that course. A student can take a same course multiple times (e.g., fail for a few time and finally pass it), but cannot take the course twice in a single semester of a given year. Students’ and staff’s names are generally NOT unique.

The college typically uses the database to create a list of course names, when they are offered, and the name of the examiner. It also emails reports for each student with their name, the names of courses they have taken and in which semester, the name of their examiner, and the grade they obtained.

In the above relations, we have the following correspondence between attributes (but not limited to): stdNo$student, examiner$ staffID and code$course.

For the following questions, do not use assumptions that are not sup-ported by the description given above. Use the Forum on the course web site to discuss the problem setting if you need clarification.

 

  • Identify all Candidate Keys for each relation.
  • Choose the Primary Key for each relation.
  • Identify all Foreign Keys for each relation. Use the following notation for each Foreign Key:

FK TableName(Attributes) References TableName(Attributes).

  • Consider the following instance for Transcript:
student course semes year grade
         
1 CSC2401 1 2010 NULL
         
1 CSC2401 2 2010 B
         
2 CSC3400 NULL 2011 A
         
3 CSC3403 3 2011 F
         
4 NULL NULL 2011 C
         
Jeff CSC2406 1 2011 HD
         
NULL CSC8407 2 2011 NULL
         

 

Note: Please do not use functional dependencies to answer Questions (a), (b) and (c). You need to identify keys based upon the description given above only.

Indicate which row(s) break the relational integrity rules, and why (there may be more than 1 reason per row).

Punjab Assignment Help

Buy Online Assignment Help services for IT ASSIGNMENT with Punjab Assignment Help at punjabassignmenthelp@gmail.com

Leave a Reply

Your email address will not be published. Required fields are marked *