Download cheat notes for database management systems and more Cheat Sheet Database Management Systems (DBMS) in PDF only on Docsity!
PPT 1
Introduction to Database What is Database? A database is a collection of related data. ▶ A database represents some aspect of the real world, sometimes called the universe of discourse (UoD). ▶ A database is a logically coherent collection of data with some inherent meaning. ▶ A database is designed, built, and populated with data for a specific purpose. What is a DBMS? A database management system (DBMS) is a computerized system that enables users to create and maintain a database. Main function of a DBMS: ▶ Data definition: ▶ Data creation and maintenance: ▶ Data retrieval: ▶ System administration Why use DBMS? Data Integrity Increase reliability. Reduced application development time Data Independence Security Concurrent access What options do we have instead of databases? Actors, DBA , End users. , DB application programmers , DBMS implementers
PPT
Relational Model Relational Model The relational model represents the database as a collection of relations ▶ Each relation resembles a table of values or, to some extent, a flat file of records (Relation). Examples: Student table, Course table ▶ Each row in the table represents a collection of related data values (Tuple).The Course table has 4 rows or records ▶ Each relation has a set of columns (Attributes). Student table has 4 attributes ▶ The data type describing the types of values that can appear in each column is represented by a domain of possible values Compared to previous models (network, hierarchical etc): ▶ Easier to understand info in tables ▶ Casual user can write simple SQL queries ▶ Complex queries much easier to understand compared to previous models A Relational Schema R, denoted by R(A1, A2,... , An) is made up of the relation name and a list of attributes. Example: Student (Name,Student number,Class,Major) ▶ Each attribute Ai is the name of a role played by some domain (a set of values) called dom(Ai) Example: The domain of Student number attribute is the set of positive integers. ▶ A Relation State r of the relational schema R(A1, A2,... , An) is a set of tuples r = {t1, t2,... , tm} where each tuple is a set of pairs consisting of the name of an attribute and its value: tk = {A1 : v1k , A2 : v2k ,... , An : vnk } where vik ∈ dom(Ai) Relational Model Constraints Constraints that are inherent in the data model. We call these inherent model-based constraints or implicit constraints. ▶ Constraints that can be directly expressed in the schemas of the data model, typically by specifying them in the DDL (data definition language) We call these schema- based constraints or explicit constraints. ▶ Constraints that cannot be directly expressed in the schemas of the data model, and hence must be expressed and enforced by the application programs or in some other way. We call these application-based or semantic constraints or business rules.
Integrity Constraints ▶ Integrity Constraint: condition that must be true for any instance of the database. ▶ Integrity Constraints are specified when schema is defined and checked when relations are created and modified ▶ Integrity Constraint types: ▶ DomainConstraint: value has to be from a specified domain ▶ KeyConstraint: key has to be unique ▶ Entity Integrity Constraint: no part of a key can be null. ▶ Referential Integrity Constraint: foreign key value must be either null or match the primary key value. Other Schema Alteration Commands ▶ RENAME TABLE ▶ DROP TABLE ▶ CREATE DATABASE ▶ ALTER DATABASE ▶ TRUNCATE SQL-DML- 1 - BASICS Insert – update—delete—query SQL-DML- 2 - multiple tables SQL SELECT - Continued Querying From Multiple Tables Ordering Rows SQL-DML- 3 - more operations Operators Comparisons Nested queries SQL DML Joins Cross Products Conditions on Joins Outer Joints
Example Databases SQL DML Aggregate Operators Group By Group By and Having WITH operator Recursive Queries ALL and ANY
