Database Management

Outline • Final exam next class • In classroom • Online, bring your laptop • Review final exam coverage 2 Review the final exam format • Final exam will be in the classroom • Start the exam on time • Bring your laptop and charger. Make sure wifi is working • Join 5 minutes earlier! • Format • True/False • Multiple choices • Essay • We will have the class on day 30 • Review the final exam answers • Web hosting • XAMPP • Backup project 3 Review the final exam coverage – SQL • SQL • DML, DDL, DCL • Data types, constraints, P.K., F.K., indexing • Joins: inner, outer, natural, Cartesian Product • View, updatable view, materialized view • NULL and logical operations • Variables, stored routines • Do the SQL exercise paper, homework • SQL error code 150 – foreign key errors • 4 situations: refer to day 14 slides 4 Review the final exam coverage – Concepts & Design • Database lifecycle – from requirement to maintenance • Database design – logical and physical • ER Diagrams and examples • Example tables with values for ERD • Convert ERD to tables and examples • Web database application • SQL injection • Project • Team study • Inputs, outputs, ERD, CREATE tables, functions 5 Review the final exam coverage – Concepts & Design • Concepts of normalization, lossless join • Functional dependency • Normalization process, normal forms • Database indexing • Database & filesystem • RAID, SSD • Transactions • DBA responsibilities and tasks 6 Exercise: Database design • Example: • Systems: Amazon, Keanwise, Ebay, Facebook, .. • Student, course, registration • Customer, employee, car model • Employee/manager, department, project • Product, vendor, store • Conceptual questions • Indexing, search time • Keys • Database web applications 7 Exercise: E-R diagrams • Topics: • Entity, Attributes, Relationship • Draw E-R diagrams from requirements • Translate E-R diagram to tables • Translate tables to E-R diagram • Draw E-R diagram for the following tables in dreamhome • Staff, Branch, PropertyForRent, Viewing 8 Exercise: ER-Diagram & tables • Draw E-R diagram base on these tables • Student (SID, Name, Address) • Course (CID, Name, credits) • Registration (SID, CID, year_semester) • Write the SQL CREATE statements • Insert records • Student(1, ’Austin’, ‘1000 Morris Ave Union, NJ’) • Course (‘CP2231’, ‘Java2’, 4) • Registration (1, ‘CPS2231’, ‘2016F’) 9 Exercise: SQL • Refer to the exercise paper • SELECT • UPDATE • DELETE • INSERT • CREATE table, view, stored routines, DROP • ALTER command – add, remove columns, constraints • Inner Join, outer join • Group by • Order by • Subquery • Grant, Revoke 10 Review midterm 1 and 2 topics • Midterm 1 and 2 topics are also part of the final exam. • Please refer to day 9 and day 17 slide sets for midterm review 11 Review homework • Part of the final exam 12 Q&A 13