Database terms DBMS Database Management System System catalog stores schema constraints users privileges etc Program data independence application programs are independent of structure of data files Program operation independence application programs are independent of implementation of data structures Transaction processing concurrent access of DB by multiple users Data model concepts used to describe structure of database data types relationships constraints Schema vs instance Levels of abstractions Internal Conceptual External Physical data independence Logical data independence Two tier client server architecture Three tier client server architecture Client Application server Database server and its advantages ER EER Model Concepts Entities Attributes simple composite multi valued Keys Relationships Degree Cardinality Total Partial participation Min Max constraints Weak entities and their uses Classes subclasses Specialization Generalization Categories ER and EER notation Relational model Database is a collection of relations Relation Table Attribute Field Column Domain Value set Primary Secondary and Foreign keys Constraints Atomicity of values and the domain constraint Key constraint Integrity constraint Entity and referential integrity constraints Relational Algebra Collection of operations on relations Select Project Join Product set operations Select filter rows matching a condition from a relation Project choose columns of interest from a relation Join Product combine rows of multiple relations based on matching conditions Natural join and equi join Aggregate operator F with operators average min max sum count Relational Calculus Define a set that satisfies a given predicate Variables range over tuples Tuple RC or domains Domain RC Quantifiers Existential and Universal Expressions that define infinite sets are unsafe and should be avoided Use care when writing queries using or when using not with Mapping from ER EER to Relational model Each entity or weak entity is assigned a table with its attributes Composite attributes have to be broken into their components to satisfy the atomicity constraint of the relational model Each multivalued attribute is assigned a separate table Each M N binary relation and higher degree relation is assigned a table with the primary keys of the participating entities stored in it as foreign keys cross reference approach For 1 N and 1 1 binary relations we can choose between cross reference approach and foreign key approach migration For EER constructs we choose one of four ways of mapping classes subclasses SQL DDL commands Create Table Alter Table Drop Table Simple queries SELECT FROM WHERE Operators IN NOT IN EXISTS NOT EXISTS IS NULL Nested queries using IN EXISTS Aggregate queries SELECT FROM WHERE GROUP BY HAVING Aggregate operators SUM MAX MIN AVG COUNT Only aggregates and grouping fields are allowed in SELECT line of aggregate queries only aggregate conditions are allowed in HAVING
View Full Document
Unlocking...