Database Concepts and SQL - L3
Database Design Process
Conceptual Model: Entity-Relationship Modelling (ERD), Crow’s Foot notation.
Logical Model: Convert conceptual model to detailed logical model ready for DB implementation.
Relational Model: Convert ER model to tables, apply Normalisation.
SQL: Use Relational Model to implement database.
Data Definition Language (DDL) defines the tables.
Data Manipulation Language (DML) queries/updates the tables.
Entity Clustering
Grouping of entities and relationships, especially when ERD is large.
Example: OFFERING (SEMESTER, COURSE, CLASS), LOCATION (ROOM, BUILDING).
Relational Model
Relation: two-dimensional table.
Tuples: rows.
Attributes: column headers.
Keys: identifiers for a row.
Schema: name of relation and its attributes.
Database schema: set of schemas for all relations in a database.
Data Dictionary (Metadata): describes schema details.
Rules for Relations
Unique name.
Unique attribute names.
Atomic attribute values.
Unique rows.
Irrelevant column order.
Irrelevant row order.
Data Dictionary
Describes schema details (table purpose, column data types) and other database objects.
ANSI/ISO SQL Data Types
CHAR, VARCHAR: Character strings.
INTEGER: Integer numbers.
NUMERIC, FLOAT: Decimal numbers.
DATE, TIME, TIMESTAMP: Date and time.
Keys
Primary Key: Unique identifier for a table.
Candidate Key: A set of columns that can uniquely identify rows.
Alternate Key: Candidate key not selected as primary key.
Foreign Key: Links to the primary key of another table.
Database Integrity Constraints
Entity Integrity: Primary key must be unique and not NULL.
Referential Integrity: Foreign key must be NULL or match a primary key in the referenced table.
Domain Integrity: Data values must conform to predefined data type and constraints.
Logical Model to Relational Model
Translate entities (including composite entities) into relations (tables).
Attributes of the entity become attributes of the relation.
Translate relationships to FK links.
Mapping Relationships
1:1 Unary/Recursive: Composite entity becomes a relation.
1:M Unary/Recursive: Use a recursive FK.
M:N Unary/Recursive: Composite entity to break down the relationship.
Supertype/Subtype Relationships
Avoid one big relation due to data integrity and storage space issues.
Avoid separate relations for each subtype, which scatters common attributes.
Create separate relations for each subtype and the supertype.
Header-Details Pattern
Used in e-commerce and invoicing to avoid repeated data.
General Mappings
ER entities become relational schemata.
Entity relationships become relational schema or FK references.
ER attributes become column headers.
SQL
Data Definition Language (DDL): CREATE, ALTER, DROP.
Data Manipulation Language (DML): SELECT, INSERT, UPDATE, DELETE.
Transaction Control Language (TCL): COMMIT, ROLLBACK, SAVEPOINT.
SQL Statements
CREATE SCHEMA AUTHORIZATION Creator;
CREATE TABLE tablename (column1 data type [constraint], …);
Constraints
PRIMARY KEY, FOREIGN KEY, NOT NULL, UNIQUE, DEFAULT, CHECK.
SELECT statement
SELECT [DISTINCT | ALL] columnexpression [AS newname] [, …] FROM tablename [alias] [WHERE condition] [GROUP BY columnlist] [HAVING condition] [ORDER BY column_list];
WHERE Clause Operators
=, <, <=, >, >=, <>, BETWEEN, LIKE, IN, EXISTS, ISNULL.
WHERE Clause Logical Operators
AND, OR, NOT.
ORDER BY Clause
Sort query results by column(s).