Database terms• DBMS: Database Management System• System catalog: stores schema, constraints, users , privileges, etc.• Program-data independence: application p rograms are independent of structure of data files• Program-operation independence: application programs are in dependent of implementationof data structures• Transaction processing: concurrent access of DB by multiple users• Data mod el: concepts used to describe structure of database (data typ es, relationships, con-straints); Schema vs instance• Levels of abstractions: Internal, Conceptual, External; Physical data independence, Logicaldata independence• Two-tier client/server architecture• Three-tier client/server architecture (Client, App lication server, Database server), and itsadvantagesER/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 notationRelational 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 con-straint, Entity and referential integrity constraintsRelational Algebra• Collection of operations on relations: Select (σ), Proj ect (π), Join (⊲⊳), Product (×), setoperations (∪, ∩, −)• Select: filter rows matching a condition from a relation• Project: choose columns of interest f rom 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, countRelational Calculus• Define a set that satisfies a given predicate• Variables r ange 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” w ith ∃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 con-straint 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 primarykeys of the p articipating entities s tored in it as foreign keys (cross reference approach)• For 1:N (and 1:1) binary relations, we can choose between cross reference approach and foreignkey approach (migration)• For EER constructs, we choose one of four ways of m ap ping classes/subclassesSQL• DDL commands: Create Table, Alter Table, Drop Table• Simple queries: SELECT ... FROM ... WHERE ...• Operators: IN, NOT IN, EXISTS, NOT EXIST S, IS NULL• Nested queries usin g IN/EXIST S• 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; onlyaggregate conditions are allowed in
View Full Document
Unlocking...