INFO90002 Database Systems and Information Modelling Lecture 24 Exam Preparation David Eccles INFO90002: Database Systems & Information Modelling © University of Melbourne 2021 Subject Revision -2- • High level overview of the semester • Focus on what is assessed • All content covered in Lectures, Tutorials, Tutorial Solution, Quizzes & Assignments and can be assessed INFO90002: Database Systems & Information Modelling © University of Melbourne 2021 Week 1 The Development Lifecycle -3- • The Database Development Cycle • The database development life cycle • Three stages of database development (conceptual, logical, physical) • The ability to analyse a case study INFO90002: Database Systems & Information Modelling © University of Melbourne 2021 Week 2 Intro & Conceptual Modelling -4- • L3. Introduction to Modelling • Need to be able to draw conceptual diagrams on your own ‒ Given a problem, determine entities, attributes, relationships ‒ What is key constraint and participation constraint, weak entity? ‒ Determine constraints for the given entities & their relationships ‒ You must use CHEN notation for conceptual models • L4. Logical & Physical ER Modelling • Be able to model a case study from conceptual to instance and all stages in between (conceptual, logical, physical, implementation and instance) • Translate conceptual (ER) into logical & physical design • Understand integrity constraints • Use DDL of SQL to create tables with constraints INFO90002: Database Systems & Information Modelling © University of Melbourne 2021 Week 3: ER Models & Normalisation -5- • L5 Modelling with MySQL Workbench • Be able to use a modelling tool • Need to be able to draw conceptual, logical and physical diagrams • SQL DDL CREATE TABLE statements • L6 Normalisation • Normalisation Process (1NF -> 2NF -> 3NF) • Anomalies • Armstrong's Axioms • Functional dependencies • Denormalisation INFO90002: Database Systems & Information Modelling © University of Melbourne 2021 Week 4: Data Types & ER Modelling -6- • L7: Data Types and Data Modelling • Correct Datatype choice & Precision is required for Physical modelling • Writing SQL DDL CREATE statements • Normalise to 3NF (from Logical model) • Distinguish between & identify • Conceptual (chen notation) • Logical Relation model • Logical ER model (crows foot) • Physical ER model (crows foot) • Implementation (DDL) • Instance INFO90002: Database Systems & Information Modelling © University of Melbourne 2021 SQL Weeks 4 & 5 -7- • SQL • SELECT • DDL (CREATE ALTER DROP TRUNCATE) • DML (INSERT UPDATE DELETE) • VIEWS • DCL (GRANT, REVOKE) • JOINS • INNER JOIN • NATURAL JOIN • LEFT OUTER | RIGHT OUTER JOIN • CARTESIAN JOIN • RELATIONAL DIVIDES • DOMAIN CONSTRAINTS (Unique, Not Null, Data Type) • REFERENTIAL INTEGRITY CONSTRAINTS (Foreign Key, Primary Key) • CONCEPTS: Key, Superkey, Candidate Key, Primary Key INFO90002: Database Systems & Information Modelling © University of Melbourne 2021 Advanced Database Concepts -8- • The remainder of the semester focussed on theoretical ideas • It is important to demonstrate more than memorisation • Apply concepts • Demonstrate practical application of theoretical ideas • Synthesize knowledge in identifying and solving problems INFO90002: Database Systems & Information Modelling © University of Melbourne 2021 Week 6 Applications -9- • L11 Database Applications • Identify the limitations of SQL • Distribution of Processing Logic (thin, fat, distributed) • Database Architectures (1-n tier architecture) • Web languages • HTML CSS SQL JAVASCRIPT • Web architecture • HTML elements • Be able to interpret a basic static HTML page •