Download
1 / 24
240 likes | 399 Vues
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
E N D
UTS DATABASE Chris Zaharia
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
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 *
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
One-to-many Relationship ERD Has
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
Many-to-many Relationship ERD Contains Part of
Queries 1. Show all undergraduate courses. SELECT courseID, courseName, courseGrad FROM Zaharia_UTS_Course WHERE courseGrad = 'Undergraduate';
Queries 2. Show all subjects with requisites and display their requisites. SELECT subjectID, subjectName, reqSubject FROM Zaharia_UTS_Subject NATURAL JOIN Zaharia_UTS_SubjectRequisite;
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;
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;
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 );
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';
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)
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')),
SQL Actions Examples • On Delete Cascade CONSTRAINT Zaharia_UTS_CourseUACCode_CourseIDFK FOREIGN KEY (CourseID) REFERENCES Zaharia_UTS_Course ON DELETE CASCADE ON UPDATE CASCADE
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 !
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
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
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;
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';
