DBMS

Categories of SQL commands

Data Definition Language (DDL) Data Manipulation Language (DML) Data Query Language (DQL) Data Control Language (DCL) - Administrator Transaction Control Language (TCL) - Administrator

ER model consists of

Entity (store data) Attribute - one or more of them are selected as Identifier (describe data) Relationship (to associate data)

To convert and save a relational model into a DDL file, do

Go to "File->Data Modeler->Export->DDL File (physical model)

Number

INTEGER: a whole number in the range of (-2**31) to (2**31)-1. NUMBER: a decimal number with up to 38 significant digits in the range of -(10**125) to +(10**125).

Steps of creating tables

Identify data types for attributes Identify columns that can and cannot be null Identify columns that must be unique (candidate keys) Identify primary key-foreign key mates Determine default values Identify constraints on columns (domain specifications) Create the table and associated indexes We must design all the tables in 3NF

binary

One-to-many (1:M): The primary key in the "one" side table is added in the "many" side table as a foreign key. many-to-many (M:N): *A many-to-many relationship between two tables requires a third/associative. *The "many" must be on the associative table side one-to-one (1:1) *treated as a special one-to-many *the association in one direction (optional) *in the other direction (mandatory)

What Can SQL do?

SQL can execute queries against a database SQL can retrieve data from a database SQL can insert records in a database SQL can update records in a database SQL can delete records from a database SQL can create new databases SQL can create new tables in a database SQL can create stored procedures in a database SQL can create views in a database SQL can set permissions on tables, procedures, and views

Integrity Rules (integrity constraints)

Three important integrity rules: -Domain integrity -Entity integrity -Referential integrity

4G languages are simple and command-like languages mostly used to manage data and generate data reports.

True

A data model does not imply ordering

True

Access doesn't has logical or relational model

True

After we create an ER model, we convert it to a real-world database using (Oracle, Access, MySQL)

True

An attribute's meaning must be consistent

True

Best practice is that we should start to apply the design rules as early as we can, that is, applying the design rules in the logical model design first.

True

Database development is usually a part of SDLC.

True

ER model (and all models in IS) is a graphic model for the database logical design

True

In order to build a operational database, we must follow the design rules.

True

Relational database is established on a solid theoretical foundation with a series of rules.

True

The FK generally goes on the "M" side in the one-to-many relationship.

True

When the logical model is converted to the relational model, the entities become tables which have columns as attributes and rows as entity instances

True

When we design a relational database, we start with a ER model

True

Without referential integrity enforced, the relationship is broken

True

the entity is located at the left side of the line in the diagrams

True

Domain Integrity

all values must have the same Data Type.

surrogate key

an extra column acting a primary key

optional

an instance is optional (zero number of instances)

mandatory

an instance required

Why do these anomalies exist?

because there is partial functional dependency in the table, which results in duplication and an unnecessary dependency between the entities.

Why we cannot create many-to-many relationship by only using two tables?

because this creates an one-to-many relationship.

Table Schema

is a data structure that actually stores data, and thus a well structured data table

RDBMS

is a database management software that stores and manages data in a collection of tables which may be associated/related with each other using primary keys and foreign keys. - Popular RDBMSs are Oracle, IBM DB2, MS SQL Server, MySQL, MS Access.

physical model

is a detailed enough data structure that can be directly built in a DBMS.

unary

is a relationship between instances of one entity or between different records/rows in the same table.

Domain

is a set of data values that are categorized into a group in which all the values have the same data type in a business context.

A Table

is a two-dimensional structure composed of rows and columns

In one-to-many relationship

is always one direction since in two tables only one table provides primary key.

strong entity (independent entity)

is an entity that represents a data object that can exist on its own.

weak entity (dependent entity)

is an entity whose existence depends on the presence of another entity.

A partial functional dependency

is that a nonkey attribute is functionally dependent on part (but not all) of the primary key.

Relationships between tables in the relational model are established by:

matching the column(s) in one table to the column(s) in another table.

Normalization helps

minimize data redundancy. simplify enforcing referential integrity. make SQL data operation easier. create better relational model that is expandable.

Scale

number of digits to the right of the decimal point

Relational Algebra

paves the theoretical foundation for SQL - a relational database programming language

Oracle SQL Developer

provides three levels of data models: - Logical Model > general description of the data structure - without needing to address data characteristics - Relational Model > further details, by turning an entity to a table with columns & primary key defined - Physical Model > shows enough detail of how the database is built (easily converted the database).

Third Normal Form (3NF)

requires that * entities meet 2NF requirements PLUS * any transitive dependencies have been removed (i.e., no nonkey attribute determines another nonkey attribute).

Constraints

rules that entities depend on to be associated with each other.

Precision

total number of digits in the number

To create a relationship between table A and table B

we match the value of the primary key in table A to the value of the foreign key in table B.

denormalize

we violate the 3NF rule on purpose

Database development includes

(1) analysis (2) design (3) development (4) deployment

There are three modification anomalies:

(1) insert anomaly (unrelated) (2) delete anomaly (A record cannot be deleted without deleting info) (3) update anomaly (Cannot update info without changing info)

Cardinality constraints (relationships)

(1:1) - one instance of entity A can be related to only one instance of entity B [vv] (1:M) - one instance of entity A can be related to many instances of entity B, [not vv] - always one direction (M:N) one instance of entity A can be related to many instances of entity B [vv] - association table needed

Boyce-Codd Normal Form (BCNF)

- BCNF is stronger 3NF and also called 3.5NF. - BCNF is 3NF and requires all data in a table depends the primary key ONLY - not on any other fields in the table.

When an entity is converted to a table

- Each entity becomes a table - The entity name becomes the table name - Each attribute becomes a column - The identifier becomes the primary key

Entity of ER model

- Entity is an unique and distinct object used to collect and store data. - person, place or thing in the environment. - An entity becomes a table in a relational database such as Oracle, Access. - Entity names must be unique.

Comment

- Not parts of SQL commands -- for single line /* */ for block of text

Two types of attributes in an entity

- Required attribute: must have a value and cannot be left empty. - Optional attribute: does not require a value and can be left empty.

Text (up to 2GB)

- VARCHAR: variable length, alpha-numeric, <=4000 char's, Oracle stores only characters entered. - CHAR: fixed length, alpha-numeric, <=2000 char's, Oracle adds trailing spaces, default is (1).

Alternative Terminology

- When we go from the model to the database (DBMS), the terminology changes. - Entity becomes Table - Attribute becomes Column or Field - Entity Instance becomes Record or Row - Relationship becomes a common Column (in each table at both ends of the relationship).

Foreign Key

- a column(s) or field(s) in a table which serve as the primary key in another table. - the column(s) in table B that refers to the primary key in table A.

Entity-Relationship (ER) model

- a data model at the first level abstraction in the relational database design. - called a conceptual logical model. - describes the data structure of a relational database ( collection of entities and relationships) - Model is called Logical Model in Oracle - consists of (Entity, Attributes, Identifier, Relationship)

transitive dependency

- a functional dependency between the primary key and one or more nonkey attributes that are dependent on the primary key via another nonkey attribute.

First Normal Form (1NF)

- any multivalued attributes (repeating groups) have been removed - i.e., each table cell must has ONLY one value - a relation must be in 1NF. - The 1NF doesn't remove anomalies, still has (insertion, deletion, update)

Normalization Rules

- are used to remove anomalies

Modification Anomalies (problem)

- cause data inconsistency, inaccuracy, data losing, or data operation failure. - results from data (field) redundancies and dependencies in a table.

Two steps to convert a unary one-to-many in a logical model to a relational model

- convert the entity to the table - add a foreign key column which refers to the primary key in the same table (recursive foreign key)

Three steps to convert a 1:1 relationship in a logical model to a relational model:

- convert two entities to two tables - use the PK in the "mandatory" side table as the FK in the "optional" side table - create a unique link between the FK and the PK in the "optional" side.

To remove transitive dependencies

- create a new relation - move all of the attributes that are functionally dependent - leave the attribute that serves as a primary key

Business rules

- define relationships in ER model. - brief, precise, and unambiguous description of a policy, procedure, or principle. - description of operations that help to create and enforce actions. - used to create relationships and constraints. - derived from written and verbal sources - specify the cardinality and participation for ER model

Data Definition Language (DDL)

- defines database schemas (e.g., tables, relationships, constraints). Commands: - CREATE table, view and others (e.g., index, function, store procedure, trigger) - DROP (delete) tables from a database - ALTER (change) database schemas (e.g., adding a new column in a table) - TRUNCATE (remove) all records from a table - COMMENT (add comments) on the data dictionary - RENAME objects (e.g., table, index, function, view, etc.) in a database

Attribute of ER model

- describes characteristic of an entity. - An entity can have many attributes. - is a discrete data element. - must be smallest piece of data that could possibly be used in a query. - becomes a column in a relational table. - unique within an entity, but can be used in different entity. - Attribute names must be meaningful. - Order of attributes is irrelevant.

data type

- determines what type of data values can be stored in a table column and how the values are stored, - defines the operations that can be performed on those values. - enforces the domain integrity - the values in one column must be in the same data type.

Logical design

- focused on [effectiveness] of data usage for business purposes (easy to access & operate). - independent on a particular DBMS.

(2) Database design

- follows database analysis. - transforms database requirements to specific database structure. - includes logical design & physical design.

Data Model (logical, an abstraction)

- graphical, logical representation of data of structure & relationships among data in the relational database. - goal: identify the facts to be stored in the database & associations between facts. - must address business needs on data. - is an iterative and incremental (progressive) process - address different level of abstraction of different level of detail for a logical design.

Referential integrity

- guarantees the relationship between tables are valid. - value of the foreign key must match the value of the primary key in another table - primary key in table A changes its foreign key must be changed in table B. - is enforced via foreign key (FK).

a many-to-many relationship

- implemented with two one-to-many relationships - One-to-many + One-to-many = Many-to-many

is necessary for data storage

- integrity - consistency - accuracy - operation - effectiveness - efficiency

Candidate Key

- is a column or a combination of columns that can be selected as a primary key. - There can be multiple candidate keys in a table and one of them is selected as the primary key.

Cardinality

- is a constraint on a relationship - expresses the minimum and maximum number of entity instances associated with its related entity instances - applies on both entities (sides) in a relationship

Normalization

- is a formal procedure to determine what attributes should be classified into one table without any anomalies. - Valid tables must be in normal forms created by following normalization rules.

Entity Relationship Diagram (ERD)

- is a graphic representation of the logical structure of relational database - ER model is represented by (ERD) - graphic models (Chen , Crow's Foot , UML Class)

An entity instance

- is a particular occurrence of an entity. - An entity instance becomes a row/record in a table in the relational database.

Relation

- is a special case of a table, which is defined with some characteristics; all relations are tables, but not all tables are relations. - is a specific table, and it is not a relationship.

SQL

- is a standard 4G (4 Generation) programming language for relational database management systems (RDBMS)

Identifier of ER model

- is an attribute (or combination of attributes) - uniquely identifies every instance of an entity (or row in a table) - becomes the primary key in a relational table - can be created if there is no obvious attribute(s) (preferred!) - denoted by an asterisk or "PK" or solid underline. -

A candidate key

- key is any attribute that could be used as the unique identifier (the primary key). - Multiple may exist.

Two levels of data models are used, logical model and physical model

- logical data: a general description of the data structure (entities & relationships) - physical data: depicts enough detail of the data structure of a database.

Entity integrity

- means that every entity has a unique, non-null primary key.

Converting existing data

- not be involved in the entire SDLC process. - still need a complete database analysis.

Data Manipulation Language (DML)

- operates data in a database. Commands: -INSERT records (rows) into a table -UPDATE records (rows) in a table -DELETE records (rows) from a table

domain

- refers to a data value set which is assigned to an attribute. - Ex: domain name, meaning, data type, size (or length), and allowable values or allowable range.

Relationship of ER model

- represents an association between entities - one-to-one | one-to-many | many-to-many

Primary Key Rule

- requires each row in a table must be unique with a primary key as a unique identifier - is a column (field) or combination of columns (fields) - required in the table - needed when you modify records in the table - is used to create relationships between tables (entities).

Second Normal Form (2NF)

- requires that * entities meet 1NF requirements * any partial functional dependencies have been removed -Intuitively, group the same type of attributes ("theme") into one relation.

Logic database

- starts with data modeling. - data modeling describes the logical structure of a database.

With the primary key and the foreign key

- table A and table B can be associated - a relationship between tables A and B is created - The relationships can be (1:1) - (1:M) - (M:N)

Physical design

- technical with focus on the [efficiency] of data (operations & performance). - involves database software & hardware design.

In one-to-one relationship

- the primary keys in both tables refer to each other. - two directions or symmetrical

multiplicity

- the symbol closest to the entity refers maximum (must be one or many). - other symbol refers to the minimum (zero or one).

(1) Database analysis

- to collect business requirements for data storage and data usage - to addresses business demands on data management (e.g., storing and operating data)

Entity Integrity

- to have a unique identity, all primary key entries must be unique and no part of a primary key may be null.

Relational database

- use a relational data model [ER Model] to address logical structure of database. - must meet the business requirements on data storage and operations.

Crow's Foot Model

- uses a rectangle to represent an Entity - "PK" indicates that the attribute is primary key or identifier. - Relationships are presented as a straight line

A relation is a two-dimensional table that has the following characteristics:

-Rows contain data about an entity - Columns contain data about attributes of the entity - All entries in a column are of the same kind Each column has a unique name - Cells of the table hold a single value - The order of the columns is unimportan - The order of the rows is unimportant - No two rows may be identical - Attributes are at the lowest unit that could be queried

Data Query Language (DQL)

-queries data in a database Command: SELECT data from tables

The top-down approach

1- Develop logical model first 2- Convert logical model to relational model(s) 3-Transfer the relational model(s) to physical model(s) 4- Last, create a database from the physical model

Object

A database table can store objects such as video, audio, image, file, etc. Oracle has some built-in object data types. We are not using the object data type in this class.

unary relationships: many-to-many (M:N)

A unary many-to-many relationship is so called "contains" which means that a given record can contains many other records in the same table, and vice versa

Systems analysis & design tools

Are used to identify database requirements: - Data Flow Diagram (DFD) - Class Diagram

----------------- Break ------------------------

CH2 starts here

Column constraints apply Domain Integrity with the following operations.

CHECK, NOT NULL, DEFAULT, UNIQUE

CREATE table

CREATE TABLE tablename ({column definition [table constraint]},... [ON Commit {DELETE|PRESERVE} ROWS]);