UTS DATABASE

1
UTS DATABASE

• Chris Zaharia

2
The Domain UTS
3
The Domain UTS

• Based on the UTS Database that contains
  information on courses and subjects
• Focuses especially on categorising courses under
  structures which contain subjects
• i.e. A course can have core, major etc
  structures that have a number of subjects in each
  structure category

http//www.uts.edu.au
4
Entity Relationship Diagram
Subject SubjectID SubjectName SubjectDesc SubjectG
rad CP Faculty SubjectEFTSL
Course CourseID CourseName CourseDesc CourseGrad
CourseAward CourseAward2 CRICOSCode CommSupport
LoadCP CourseEFTSL FacResp FacColl Location
Course
Subject
SubjectRequisite
CourseUACCode UACNumber CourseID UACType
SubjectRequisite SubjectID ReqSubject
CourseUACCode
SubjectStructureChoice SubjectID
StructureChoiceID
SubjectStructureChoice
CourseStructureChoice StructureID
StructureChoiceID
CourseStructureChoice
StructureChoice StructureChoiceID StructureChoiceN
ame
SubjectChoice
CourseStructure CourseID StructureID
StructureCP
CourseStructure
Structure
Structure StructuretID StructureType
SubjectFee SubjectEFTSL FeeComPrice FeeDomPrice
SubjectFee
SubjectStructure SubjectID StructureID
SubjectStructure
5
One-to-many Relationship
Table 1 - SubjectFee
Primary Key
Table 1 - Subject
Foreign Key
Primary Key

• Both tables have a one-to-many relationship
• Linked by a foreign key

6
One-to-many Relationship
ERD
Has
7
Many-to-many Relationship
Primary Key
Foreign Key
Table 1 - Subject
Table 2 - Structure
Table 3 - SubjectStructure
Primary Key
Foreign Key

• Both one-to-many paired
• Relationships (T1,T3 T2,T3)
• Creates a many-to-many
• relationship

• Table 1 and Table 2
• Primary keys are foreign
• Keys in table 3

Primary Key
8
Many-to-many Relationship
ERD
Contains
Part of
9
Queries

• 1. Show all undergraduate courses.
• SELECT courseID, courseName, courseGrad
• FROM Zaharia_UTS_Course
• WHERE courseGrad 'Undergraduate'

10
Queries

• 2. Show all subjects with requisites and display
  their requisites.
• SELECT subjectID, subjectName, reqSubject
• FROM Zaharia_UTS_Subject NATURAL JOIN
  Zaharia_UTS_SubjectRequisite

11
Queries

• 3. Show subjects along with their structure
  types.
• SELECT SubjectID, Zaharia_UTS_Structure.StructureI
  D, StructureType
• FROM Zaharia_UTS_Structure, Zaharia_UTS_SubjectStr
  ucture
• WHERE Zaharia_UTS_Structure.StructureID
  Zaharia_UTS_SubjectStructure.StructureID

12
Queries
13
Queries

• 4. Show the average of the Commonwealth and
  Domestic fee price for each subject whose fee
  average is over 5000
• SELECT subjectID, avg(FeeComPrice FeeDomPrice)
  AS Average_SubjectPrice
• FROM Zaharia_UTS_SubjectFee NATURAL JOIN
  Zaharia_UTS_Subject
• GROUP BY subjectID
• HAVING avg(FeeComPrice FeeDomPrice) gt 5000

14
Queries

• 5. Show all core subjects that are newer then the
  very first core subject.
• SELECT subjectID
• FROM Zaharia_UTS_SubjectStructure NATURAL JOIN
  Zaharia_UTS_Structure
• WHERE structureType 'Core'
• AND subjectID gt (
• SELECT min(subjectID)
• FROM Zaharia_UTS_SubjectStructure
• )

15
Queries

• 6. Show all courses that share the same
  graduation status as course C10219 (BBus BScIT).
• SELECT c1.courseID
• FROM Zaharia_UTS_Course c1, Zaharia_UTS_Course C2
• WHERE c1.courseGrad c2.courseGrad
• AND c2.courseID 'C10219'
• AND c1.courseID ltgt 'C10219'

16
CHECK Constraint Examples

• Check for graduation status
• CONSTRAINT Zaharia_UTS_Course_CourseGrad
• CHECK (CourseGrad IN (
• 'Undergraduate', 'Postgraduate')),
• Check value of Course EFTSL
• CONSTRAINT Zaharia_UTS_Course_CourseEFTSL
• CHECK (CourseEFTSL BETWEEN 0.5 AND 7)

17
CHECK Constraint Examples

• Check for Faculty Responsible
• CONSTRAINT Zaharia_UTS_Course_FacResp
• CHECK (FacResp IN (
• 'Business',
• 'Design, Architecture and Building',
• 'Education',
• 'Engineering',
• 'Humanities',
• 'Information Technology',
• 'Law',
• 'Nursing, Midwifery Health',
• 'Science')),

18
SQL Actions Examples

• On Delete Cascade
• CONSTRAINT Zaharia_UTS_CourseUACCode_CourseIDFK
  FOREIGN KEY (CourseID) REFERENCES
  Zaharia_UTS_Course
• ON DELETE CASCADE
• ON UPDATE CASCADE

19
SQL Actions Examples

• For example if we would delete a Course from the
  Course table
• DELETE FROM Zaharia_UTS_Course WHERE CourseID
  'C10026'
• This would also delete the Courses UAC code
  details from the CourseUACCode table, and we can
  see this after
• SELECT FROM Zaharia_UTS_CourseUACCode

No C10026 !
20
SQL Actions Examples (2)

• On Delete Restrict
• CONSTRAINT Zaharia_UTS_Subject_SubjectEFTSLFK
  FOREIGN KEY (SubjectEFTSL) REFERENCES
  Zaharia_UTS_SubjectFee
• ON DELETE RESTRICT
• ON UPDATE CASCADE

21
SQL Actions Examples (2)

• For example if we try to delete a subjects EFTSL
  from the SubjectFee table
• DELETE FROM Zaharia_UTS_SubjectFee WHERE
  SubjectEFTSL 0.125
• We get the error
• update or delete on "zaharia_uts_subjectfee"
  violates foreign key constraint
  "zaharia_uts_subject_subjecteftslfk" on
  "zaharia_uts_subject
• Since the foreign key SubjectEFTSL in the
  Subject table is restricted from deleting data in
  the Subject table that shares the value
• SubjectEFTSL 0.125

22
Creating a View Example

• CREATE VIEW Zaharia_UTS_CoursePrice
• (CourseID, CourseName, CourseCP, CoursePriceCom,
  CoursePriceDom)
• AS SELECT CourseID, CourseName, LoadCP,
  CourseEFTSL 7118, CourseEFTSL 18240
• FROM Zaharia_UTS_Course

23
Creating a View Example

• Query view as if a table
• Show the subject prices for the subject Database
  Fundamentals in the BBus BScIT course
• SELECT SubjectID, ((CoursePriceCom / CourseCP)
  CP) AS SubjectComPrice, ((CoursePriceDom /
  CourseCP) CP) AS SubjectDomPrice
• FROM Zaharia_UTS_Subject, Zaharia_UTS_CoursePrice
• WHERE SubjectName 'Database Fundamentals'
• AND CourseName 'Bachelor of Business Bachelor
  of Science in Information Technology'

24
END