1 / 20

Introduction to Databases: Core Concepts, Queries, and Design Practices

Explore fundamental database concepts including data modeling, normalization, SQL queries, and database design principles. Learn about ER modeling, indexing, data protection, and security mechanisms. Master SQL programming and query optimization techniques.

loman
Télécharger la présentation

Introduction to Databases: Core Concepts, Queries, and Design Practices

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. COMP102: Introduction to Databases, 30 Dr. Muhammad Sulaiman Khan Department of Computer Science The University of Liverpool U.K. 10 May 2011

  2. Material Revision Lecture

  3. Lecture 1&2 All stuff from these lectures !!! • Data vs Information • Information system and its functions (functions define operations on information!) • Other defs: Database, DBMS, Application Program, etc. • TPM (Transaction Processing Monitor) – def, understand its role, functions, ... • Transactions (ACID), ... • Concurrency Control Service • Etc,…

  4. Lecture 3 All stuff from this lecture !!! • Data Model • Relational Data Model Terminology • Properties of relations, relational tables !!! (Rows, Columns, Cardinality etc.) • Sets, set inclusions, ... !!! • Notions of keys, examples, .... !!!!! • Questions like: table has attributes (P,Q,R,S) can (P,Q) and (Q) be both CK’s, PK’s ??? • Etc,.... • ....

  5. Lectures 4 - 7 All stuff from these lectures !!! • These are first lectures about SQL, SQL queries, etc. !!! • About 25% questions in the exam is related to SQL/SQL statements and queries !!! • Correlated subqueries !!! • Etc • .... • ....

  6. Lecture 8, 9 and 9a • DSDLC or DBSDLC: know roughly its parts and understand what they are about • Prototyping • Testing!!! • Fact-finding!!! know fact-finding techniques and understand what they are about

  7. Lectures 11 and 12 All stuff from these lectures !!! • Entity-Relationship Modeling • UML notations • E.g.: how do we calculate the multiplicity (cardinality and participation) constraints of binary and of complex relationships. • Chasm and Fan traps, … • Know about the types of relationships: Inverse, Unary, Recursive and Complex • Etc,...

  8. Lecture 13 All stuff from this lecture !!! • Three forms of Normalization (def) • How normalisation reduces redundancy and update anomalies • Functional relationship/dependency • Conversion from 1st to 2nd and then to 3rd normal form • Pay special attention to wording in the definitions!!! • ... • ...

  9. Lecture 14 • Know about: • Logical database design • Physical database design • How to identify: • Attributes • Entities • Relationships • Attribute domain • Keys!!! • …

  10. Lecture 15 All stuff from this lecture !!! • ER model into tables (the relational model): all details!!! • Types of relationships involving multiplicities • Identifying parent and child entities using participation constraints • Normalisation • Integrity constraints • Business rules • Etc...

  11. Lecture 16 • All defs, especially Specialisation/Generalisation!!! • Concepts of super class and sub class in ER Modeling. • Know roughly participation constraints and understand what they are about. • Creating tables to represent specialization/generalization • … • …

  12. Lecture 17 • Indexes • Analysing transactions • File organisations • Linear search and Binary Search • Derived data • Etc…

  13. Lecture 18 All stuff from this lecture !!! • User views and security mechanism • Controlled redundancy (denormalisation) • Monitoring and Tuning the operational system • Measuring system efficiency • Etc, ... • ...

  14. Lectures 19-20 & 21-23 All stuff from these lectures !!! • SQL, SQL, SQL • About 25% questions in the exam is related to SQL/SQL statements and queries !!! • JOINS • NULL vs unknown • Using MIN/MAX in queries • ...

  15. Lectures 24 & 25 • Constraints and types • Triggers and their types • Authentication vsAuthorisation !!! • Roles and Privileges (specially Grant in MySQL) • Roughly know Public Key Cryptography, RSA Encryption

  16. Lecture 26 & 27 All stuff from this lecture !!! • Programming with SQL • SQL injection attacks!!! • CGI • JDBC • Etc • ...

  17. Lectures 28 • Know about query optimisation • Know about relational algebra, specially the following and how they are used: • INTERSECT • EXCEPT • UNION • Selection • Projection • Etc…

  18. Lecture 29 All stuff from this lecture !!! • Know Data Protection Act 1998 • Know all 8 Principles • Other defs • Understand application of the Data Protection Act 1998 • Etc • ...

  19. Remarks • Remember: UML is the way how we draw our ER diagrams!!! For us UML diagrams are same as ER diagrams. • REMARK: Study carefully referential integrity (PK/FK—mechanism, and how they are realized in SQL) !!!

  20. Example of an exam SQL question Consider the schema: Movie(title, year, length, studioName, producer) StarsIn(movieTitle, movieYear, starName) and the two SQL queries: SELECT starName FROM StarsIn WHERE movieYear < ALL ( SELECT year FROM Movie WHERE producer = ‘Johny‘ ); SELECT starName FROM StarsIn WHERE movieYear < ( SELECT MIN(year) FROM Movie WHERE producer = ‘Johny‘ ); Which combination of the following statements are all true concerning these two SQL queries ? I. These two queries produce the same results. II. Both queries use aggregate functions. III. The first query finds all actors who starred in a movie produced before producer Johny launched any movie. IV. The second query uses a correlated subquery. A. I. only B. I. and II. only C. III. only D. I. and III. only E. II. and IV. only

More Related