J

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).