# ***DataBase System***
### Chapter 1
- **DBMS (Database Management System) Functionality**
- Define, Construct, Manipulating, Processing, Sharing
- Construct or load the initial content
- Applications interact with a database generating
* Queries : access different parts of data
* Transactions : flight reservation
- Additional provide
* Protect and Security
* "Active" Processing
* Visualization Data
* Maintainance
- **Characteristics of the Database**
- Self-describing nature of database
* DBMS catalog store data structures, types, constraints
- Insulation between programs and data (Program-data independence)
* Allows changing data structures and storage organization without changing DBMS
- Data Abstraction
* Program-data independence and program-operation
-Support mutiple views of the data
* Each user see different view

- **Advantage of using database**
- Controlling redundancy
* Duplicated effort
* Waste storage
- Restricting unauthorized access to data
- Provided persistent storage for programming
- Provided storage structure
- Optimization of queries
- Mutiple interfaces
- Backup recovery
- Integrity constraints
- **Disadvantage of using DBMS**
- Main costs (High initial investment)
- Embedded system may not fit in available storage
***
### Chapter 2 Concept and Architecture
- **Data model Operations**
- Operations include basic model, user-defined operations
- Conceptual data models:
* Provided concepts close to the way many users perceive data
* Also called entity-based, objected-based
- Physical data models:
* Provide details of how data is stored in computer
- Implementation data models:
* Provide between above two, used by commercial DBMS
- Self-Describing Data Models:
* Combine description of data with data values
- **Schemas**
- Description of database
- Includes structure, data types, constraints

- Schema VS State
* Database schema only to the DBMS
* Database state is the empty state with no data
* State satisfies the structure and constraints of the database specified in schema
* Schema changes indrequently, state changes every time when database update
* Schema also called intension
* State also called extension
- Three-Schema Architecture
* Self-describing
* Program-data independence
* Mutiple views
- Three-Schema levels
* Internal Schema: describe physical storge structures and access pathes
* Conceptual Schema: describe structure and constraints
* External schemas:describe various user views
- Mapping
* Process of transforming requests and results between levels called mapping
* Programs refer to an external schema and mapped by the DBMS to internal schema
* Data extracted from the internal DBMS is reformatted to match the users external view
- **Data Independence**
- Logical Data Indenpendence
* Capacity to change the conceptual schema without changing external schemas.
- Physical Data Indenpendence
* Capacity to change the internal schema without changing conceptual schemas.
- **DBMS Language**
- Data Definition Language (DDL)
* Used DBA to specify the conceptual and internal schemas
* Some DBMS separate storage definition(SDL) and view definition language(VDL)
* Most DBMS, DDL is used to define both conceptual and external sehcmas
- Data Manipulate Language (DML)
* Used to specify data base retrievals and updates
* High level used in standalone way or embedded in programming language
* Low level must embedded in programming language
- **Databse Env**
- DBMS component modules
* Buffer management
* Stored data manager
* Runtime databse processor
* System catalog
* Concurrency control system
* Backup and recover system
- Two-Tier Client-Server Architecture
* Open Database Connectivity Standard (ODBC)
* Client and server must install client module and server module for ODBC or JDBC
- Three-Tier Client-Server Architecture
* Common for Web applications
* Intermediate Layer called "Application Server" or "Web Server"
* Server only accessible via middle tier
* Clients cannot directly access database server
- **Classification of DBMS**
- Based on the data model used
* Legacy: Network, Hierarchical
* Currently used: Relational, Object-oriented, Object-relational
***
### Chapter3 Entity-Relationship Model
- **ER model concepts**
- Entity: Basic concept for the ER model, specific thins or objects.
- Attributes: Properties to describe an entity
- Specific entity will have value for each attributes.
- **Types of attributes**
- Simple: Each entity has single stomic value. EX: SSN, Sex.
- Composite: Attribute may be composed of several components.
- Muti-valued
- stored vs derived attributes
- A attribute of an entity type for each entity must has unique value called **key attribute** of the entity type
- **Entity Set**
- Entity have collection of entities called entity set or collection
- Entity set is the current of the entities of type
- **Value Sets**
- Each simple attribute is associated with value set
Ex: Date has MM-DD-YYYY
- Value sets similar to data type in most
- an attribute A for an entity type E, value set of V
A: E -> P(V)
- Refer the value of attribute A for entity e
A(e)
- **Displaying Entity type**
- Entity type is displayed in rectangular box.
- Attributes are displayed in ovals.
- Mutivalued attributes in double ovals.
- **Relationships and Relationship type**
- Three main concepts
1. Entities
2. Attributes
3. Relationships
- Relationship relates 2 or more destinct entities
- Reltaionship of same type are grouped into a reltaionship type
- The degree of relationship type is num of participating entity types
- **Relationships vs relationships set**
- Relationship Types:
1. The Schema description
2. Indentifies the relationship name and entity types
3. Identifies certain relationship constraints
- Relationship Set:
1. Set of relationship instances
2. State of relationship type
- Relationship be readable from left to right and top to bottom
- Relationship type is diamond-shaped box.
- **Constraints on Relationshops**
- Cardinality Ratio
A. One-to-one
B. One-to-many or Many-to-one
C. Many-to-many
- **Recursive Relationship Type**
- Relationship type between same participating in distinct roles.
- Also called self-referencing relationship type
- In ER diagram, display role names to distinguish participations.
- **Weak Entity Types**
- Means that an entity does not have a key attribute and it's identification-dependent on another entity type.
***
### Chapter4 Enhanced Entity-Relationship (EER)
- **Subclasses and Superclassess**
- EER diagram extend ER diagram to represent additional called **subclassess** and **subtypes**
- Also called IS-A relationships
Ex: Secretary IS-A Employee
Subclass IS-A Superclass
- An entity **cannot** exist in database merely by being a member of a subclass
- **Specialization**
- Have several specializations of the same superclass
- Attributes of a subclass are called specific or local attributes
- **Generalization and Specialization**
- Pointing to the generalized superclass represents a generalization.
- Pointing to the specialized subclasses repersents a specialization.
- Both show in rectangles in EER diagram as entity types
- **Types of Specialization**
A. Presicate-defined: Can determine exactly entities.
B. Attribute-defined: Have membership condition on same attribute.
C. User-defined: No condition determines membership.
- **Basic Constraint**
- Disjointness Constraint:
**At most one**, specified by **d**
If not disjointness, means **more than one**, specifided by **O**
- Completeness Constraint
Total must be a member of some subclass, in double line.
Pratial allows not to belong to any subclasses, in single line.
- Two of these are independent
- 4 types of specialization/generalization
Disjoint - Total
Disjoint - Partial
Overlapping - Total
Overlapping - Partial
- **Hierarchies, Lattices**
- Hierarchy has constraint **only one** superclass, called single inheritance
- Lattice can be subclass of **more than one** superclass, called mutiple inheritance
***
### Chapter5 Relationship Data Model and Constraint
- **Characteristics Of Relationships**
- The tuples are not considered to be ordered.
- Attributes can be ordered.
- Each value in a tuple must from the domain of the attribute.
- Null value is used to represent values that are **unknown** or **not available** or **inapplicable**
- **Constraints**
- Inherent of Implicit Constraints: Based on data model
- Schema-based or Explicit Constraints: Expressed in schema by using facilities.
- Application based or semantic Constraints: Must be specified and enforced by application programs.
- **Key constraints**
- A key is superkey but not vice versa.
- Any set of attributes that includes a key is a superkey
- A minimal superkey is also a key
- **Entity integrity constraints**
- The primary key cannot have null values.
- **Referential integrity constraints**
- A constraint involving two relations
- Specify a relationship among tuples in 2 relations
- Tuples in referencing relation have attributes called FK (Foreign key)
- Delete may violate only referential integrity
***
### Chapter6
- **Basic data types**
- Numeric data types:
- Integer, INT, SMALLINT
- FLOAT, REAL
- Character-string data type:
- Fixed length:Varchar(n)
- INSERT INTO WORKS_ON_INFO(Emap_name, Proj_name, Hours_per_week )
SELECT E.Lname, P.name, W.HOurs
FROM PROJECT P, WORK_ON W, EMPLOYEE E
WHERE P.Pnumber = W.Pno AND W.Essn = E.ssn;

***
### Chapter7 Complex Queries, Triggers, Views...Modification
- **Comparsions Involving NULL and Three-Valued Logic**
- Meaning of NULL
- Unknowsn value
- Unavailable or withheld value
- Not applicable attribute
- NULL = NULL comparison is **avoided**
- **Nested Queries**

- 
-
- Use Comparison operators to compare a single value V
- (= Any) or (= Some)
- '>', >=, <, <= and <> can be combined with Any or Come
- **EXISTS and UNIQUE Functions in SQL**
- Check whether the result of a correlated nested query is empty or not.
***
### Chapter 9 Relationship Database Design and Mapping
- Mapping Algorithm
- Regular Entity Types
- Create a relation includes all the simple attributes
- Weak Entity Types
- Create a relation includes all the simple attributes and include as foreign key.
- 1 : 1 Relationship Types
- Choose one of relation include foreign key
- 1 : N Relationship Types
- Represent the participating entity type at N-side
- M : N Relationship Types
- Create new relation to represent, combine both Primiary key
- Mutivalued attributes
- Create new relationship

***
### Chapter 14 Basics of Functional and Normalization
- **Normalization of data**
- A process based on their FDs and primary keys
- Minimizing redundancy
- Minimizing the insertion, deletion, update anomalies
- **Superkey & key**
- A key K is a superkey with the additional property the removal any attribute from K will cause K not to be superkey.
- Key is a minimal superkey.
- Relationship schema has more than one key, each called candidate key.
- A prime attribute must be a member of some candidate key.
- **First Normal Form**
- Disallows
- Composite attributes
- Mutivalued attributes
- Nest relations
- Change to 1NF
- Remove attribute and place in separate relation
- Expand the key
- Use several atomic attributes
- Remove nested relation attributes onto **NEW** relation
- Propagate the primary key into it
- **Second Normal Form**
- If every non-prime attribute A in R is fully functionally dependent on the primary key.
- R can be decomposed into 2NF via the process of 2NF normalization
- **Third Normal Form**
- Transitive function dependency x -> y and y->z can derived x->z
- 3NF is in 2NF and no non-prime attribute
- R can be decomposed into 3NF
-