Database Design

user requirement

define how data is handled and stored, guided by org's rules, policies, and business procedures that set constraints on data creation, updating and removal - present using ERD

entity relationship model

A logical representation of the data for an organization or for a business area

entity relationship diagram

graphical representation of ER model

entity type

a collection of entities that share common properties or characteristics

-denoted by rectangle

entity instance

single occurrence of an entity type, person, place, object, event, etc in user environment about which company wants to maintain data

supertype

generic entity type that has a relationship with one or more subtypes

subtype

subgrouping of the entities in an entity type and shares common attributes/relationships distinct from subgrouping

attribute inheritance

a property by which subtype entities inherit values of all attributes and instances of all relationships of their supertype

-avoid redundancy and increase simplicity

generalization

process of defining a more general entity type from a set of more specialized entity types

specialization

defining one or more subtypes of supertype

attributes

properties or characteristics of an entity or relationship type

-should be unique

identifier/key attribute

an attribute whose value distinguishes individual instances of entity type

relationship instance

association representing an interaction among instances of one or more entity type

relationship type

meaningful association between entity types

-collection of relationships that share common properties/characteristics

-glue that holds together the various components of ER model

degree

number of entity types participating in relationship type

unary relationship

relationship between instances of a single entity type (recursive)

binary relationship

relationship between instances of two entity types

-most common

tenary relationship

simultaneous relationship amond instances of 3 entity types

cardinality constraints

specifies the number of instances of one entity that can (or must) be associated with each instance of another entity

total specialization rule

each entity instance of the supertype must be a member of some subtype in the relationship

partial specialization rule

an entity instance of a supertype is allowed not to belong to any subtype

overlap rule

an instance of the supertype could be more than one of the subtypes

disjoint rule

an instance of the supertype can be only ONE of the subtypes

logical database design

transforming conceptual model into logical model.

relation

named two-dimensional table of data (entity)

-consists set of names column and arbitrary of unnamed rows

-not all tables are relation

primary key

attribute that uniquely identifies each row in relation

-cannot be null

composite key

primary key that consists of more than one attribute

-can be null

foreign key

attribute in a relation that serves as primary key of another relation

normalization

process of decomposing relations with anomalies to produce smaller, well-structured relations.

-eliminates redundancy, improve data integrity, minimize modification, improve workflow, lessen cost

-1NF, 2NF, 3NF

1NF

any multivalued attribute (repeating groups) have been removed, so there is single value at intersection of each now and column of table

2NF

any partial functional dependencies have been removed (nonkey attribute are identified by whole primary key)

partial functional dependency

exists when nonkey attribute if functionally dependent on part (not all) of primary key

3NF

any transitive dependencies have been removed (non key attributes are identified by only primary key)

data definition language (DDL)

commands that define a database, including creating, altering, and dropping tables and establishing constraints

CREATE

create new table or add new records to DB using INSERT

CREATE TABLE [table name]

[col name] PRIMARY KEY,

....

DROP

PERMANENTLY deletes DB objects and their associated data

DROP TABLE [table name]

ALTER

modify structure of existing DB objects - can add, modify, drop columns, constraints or indexes from table

ALTER TABLE [table name]

ADD COLUMN [col name][data type]

ALTER COLUMN [col name] SET DATA TYPE [type]

RENAME COLUMN [col name] TO [new name]

TRUNCATE

removes ALL records from table but keeps table structure (columns constraints)

data manipulation language (DML)

commands that are used for updating, inserting, modifying, and querying the data in the database. It is the core of SQL

INSERT

add new rows of data

INSERT INTO [table name] VALUES ('...', '...')

UPDATE

modifying existing records in DB

UPDATE [table name]

SET [col name] = [value]

WHERE [col name] = [value]

DELETE

removing records from DB - can undo

DELETE FROM [table name]

WHERE [col name] = [value]

data control language (DCL)

commands help a DBA control the database; they incudes commands to grant or revoke privileges to access the database or particular objects within the database and to store or remove transactions that would affect the database

data integrity

refers to the overall accuracy, completeness, and consistency of data

integrity constraints

constraints or rules limit acceptable values and actions, to facilitate maintaining the accuracy and integrity of data

domain constraint

specifies values that appear in a column of a relation must be from the same domain

entity integrity constraint

designed to ensure the every relation has a primary key and that the data values for that primary key are all valid

-Ensure the every relation has a primary key

-Data values for that primary key are all valid

referential integrity constraint

if there is a foreign key in one relation, either each foreign key value must match a primary key value in another relation or the foreign key value must be null

denormalization

combining several logical tables into one physical table to avoid the need to bring related data back together when they are retrieved from the database

-Mechanism that is often used to improve efficient processing of data and quick access to store data

-normalized database often create many tables, and joining tables slows database processing

-improve scalability

-only use if joins are such an issue

cons:

-complex update and delete operations

-potential data integrity issues

index

data structure thats makes looking up data more efficient

-Index is itself a table with two columns: key and address of the record(s) that contain that key value

When to use index:

-Most useful on larger tables

-Specify a unique index for the primary key of each table

-Most useful for columns that frequently appear in WHERE clauses of SQL commands

-Use an index for attributes references in ORDER BY (sorting) and GROUPBY (categorizing) clauses

Pros: Faster searches, sorting, and sometimes joins.

Cons: Takes extra space and slows down inserts/updates/deletes, because the index also has to be updated.

view

virtual table is created dynamically upon request by a user

CREATE VIEW name AS

SQL statement

-UPDATE, DELETE, INSERT (modifications)

trigger

a named set of SQL statements that are considered (triggered) when a data modification occurs or if certain data definitions are encountered

DELIMITER//

CREATE TRIGGER name [BEFORE|AFTER]

[INSERT|UPDATE|DELETE] ON table name

FOR EACH ROW

BEGIN

SQL statement

END//

DELIMITER ;

stored procedure

pre-written SQL program that you store in the database itself that you can that you can call and run whenever you need it.

-Encapsulate complex logic

-Reusability

-Performance

-Security

DELIMITER//

CREATE PROCEDURE name(IN para name para data type)

BEGIN

SQL statement

END//

DELIMITER ;

CALL name

ACID

set of properties of database transactions intended to guarantee data validity despite errors, power failures, and other mishaps

atomicity, consistency, isolation, durability

atomicity

transaction is all or nothing: either all steps succeed, or none do

-Achieved using transaction logs and rollback mechanisms.

-If any step fails, the database rolls back all changes.

consistency

transaction always leaves the database in a valid state, obeying rules and constraints.

-Enforced using constraints, triggers, and rules.

-Ensures that the database always obeys things like: Foreign keys, Unique constraints, Data types

isolation

transactions don’t interfere with each other; intermediate results are hidden from other transactions.

-Achieved with locking mechanisms or multiversion concurrency control (MVCC).

-Ensures that concurrent transactions don’t see each other’s intermediate states.

-Isolation levels let you balance performance vs strict isolation.

durability

once a transaction is committed, its changes persist, even if the system crashes immediately after.

-Achieved via write-ahead logs, commit logs, or database snapshots.

-Once a transaction is committed, it’s written to disk or non-volatile storage, so it survives crashes.

transaction

a sequence of database operations that satisfies the ACID properties