week 5 sql

Lecture 5: Information Systems and Data Representation

Overview of Topics

• Physical database design and implementation
• Relational model
• Keys in relational databases
• Introduction to SQL
• Creating and dropping tables using DDL
• Using constraints in SQL

Relational Model

• Definition: An approach for managing data using structured tables and a formal language.
• Representation: Based on the mathematical concept of relations, displayed as tables.
• Features:
• Visualizes database structure.
• Provides high data independence.
• Tackles problems of data consistency and redundancy.

Relational Data Structure

• Relation: A table with columns (attributes) and rows (tuples).
• Attribute: A column of a relation (e.g., branchNo, street).
• Tuple: A row within the relation (data record).
• Domain: Set of allowable values for attributes.
• Degree: Number of attributes in a relation.
• Cardinality: Number of tuples in a relation.

Terminologies in Relational Databases

• Formal terms:
• Relation = Table
• Tuple = Row = Record
• Attribute = Column = Field

Properties of Relations

• Unique relation names.
• Each cell holds one atomic value.
• Each attribute has a unique name.
• Values of an attribute come from the same domain.
• No importance in the order of attributes or tuples.

Relational Keys

• Superkey: An attribute(s) uniquely identifying a tuple.
• Candidate Key: A minimal superkey. Example: branchNo is a candidate key.
• Primary Key: Selected candidate key to uniquely identify tuples.
• Alternate Key: Candidate keys not selected as primary key.
• Foreign Key: Attributes that link to the primary key of another table.

Null Values

• Null: Indicates an unknown or not applicable value. Not equal to zero or empty strings.

Integrity Constraints

• Entity Integrity: Primary key values cannot be null.
• Referential Integrity: Foreign key must match a candidate key in its base relation or be null.
• General Constraints: User-specified rules for data integrity.

Introduction to SQL

• SQL: Standard database language for defining and manipulating data.
• Used for data creation, management, and querying.

SQL Components

• Data Definition Language (DDL): Defines database structure and controls data access.
• Data Manipulation Language (DML): Handles the retrieval and modification of data.

SQL DDL Commands

• CREATE, ALTER, DROP, RENAME, TRUNCATE.

SQL DML Commands

• SELECT, INSERT, UPDATE, DELETE, MERGE.

SQL Data Types

• Defines the type of data attributes can hold:
• Integer, character, monetary, date/time, binary strings.
• Character Data Types:
• CHAR(size): Fixed-length.
• VARCHAR(size): Variable-length.
• Numeric Data Types:
• NUMERIC, DECIMAL, FLOAT, DOUBLE, BIGINT, etc.
• Date/Time Data Types:
• DATE, TIME, TIMESTAMP, etc.

Creating Tables in SQL

• Syntax:

  CREATE TABLE table_name (
      column1 data_type(size),
      column2 data_type(size),
      ...
  );

• Example:
• Create Students table:

  CREATE TABLE Students (
      ROLL_NO int(3),
      NAME varchar(20),
      SUBJECT varchar(20)
  );

SQL Constraints

• Ensure data integrity:
• NOT NULL: Column must contain a value.
• UNIQUE: All values in a column are distinct.
• PRIMARY KEY: Combination of NOT NULL and UNIQUE. Uniquely identifies each record.
• FOREIGN KEY: Connects two tables.
• CHECK: Ensures certain conditions for column values.
• DEFAULT: Sets a default value if none provided.
• INDEX: Accelerates data retrieval.

SQL Drop and Alter Commands

• DROP TABLE: Used to remove a table.

  DROP TABLE table_name;

• TRUNCATE TABLE: Deletes data but keeps the table.

  TRUNCATE TABLE table_name;

• ALTER TABLE: Modifies an existing table.
• Example: Add a column:

  ALTER TABLE Emp ADD dateOfBirth date;

Examples of Creating Tables with Foreign Keys

• Creating Orders Table:

  CREATE TABLE Orders (
      orderID int,
      orderNo int NOT NULL,
      personID int NOT NULL,
      constraint o_oid_pk PRIMARY KEY (orderID),
      constraint o_pid_fk FOREIGN KEY (personID) REFERENCES Persons(personID)
  );

Exercise: Logical ERD Table Creation

• Create Dept and Emp tables with appropriate constraints to ensure data integrity.
• Example:

  CREATE TABLE Emp (
      empId INT(6),
      fName VARCHAR(50) NOT NULL,
      lName VARCHAR(50) NOT NULL,
      email VARCHAR(100) UNIQUE NOT NULL,
      hireDate DATE,
      comm_pct DECIMAL(2,2),
      deptNo INT(4) NOT NULL,
      constraint e_eid_pk PRIMARY KEY (empId),
      constraint e_dno_fk FOREIGN KEY (deptNo) references Dept(deptNo)
  );