1 / 24

UTS DATABASE

UTS DATABASE. Chris Zaharia. The Domain – UTS. 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

boaz
Download Presentation

UTS DATABASE

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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 Course CourseID CourseName CourseDesc CourseGrad CourseAward CourseAward2 CRICOSCode CommSupport LoadCP CourseEFTSL FacResp FacColl Location Course Subject SubjectID SubjectName SubjectDesc SubjectGrad CP Faculty SubjectEFTSL * Subject SubjectRequisite CourseUACCode CourseUACCode UACNumber CourseID * UACType SubjectRequisite SubjectID * ReqSubject * SubjectStructureChoice SubjectID * StructureChoiceID * SubjectStructureChoice CourseStructureChoice StructureID * StructureChoiceID * CourseStructureChoice SubjectChoice StructureChoice StructureChoiceID StructureChoiceName CourseStructure CourseStructure CourseID * StructureID * StructureCP Structure Structure StructuretID StructureType SubjectFee SubjectEFTSL FeeComPrice FeeDomPrice SubjectFee SubjectStructure SubjectStructure SubjectID * StructureID *

  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.StructureID, StructureType FROM Zaharia_UTS_Structure, Zaharia_UTS_SubjectStructure 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) > 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 > ( 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 <> '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 Course’s 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 subject’s 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

More Related