Introduction Database Management Systems Database Management System DBMS
Introduction Database Management Systems
Database Management System (DBMS) n Collection of – interrelated data and – set of programs to access the data Convenient and efficient processing of data n Database Application Software n Farkas CSCE 824 2
Evolution of Database Systems n n Farkas Early days: customized applications built on top of file systems Drawbacks of using file systems to store data: – Data redundancy and inconsistency – Difficulty in accessing data – Atomicity of updates – Concurrency control – Security – Data isolation — multiple files and formats – Integrity problems CSCE 824 3
Abstraction n View level: different perspectives – application programs hide irrelevant data n Logical level: data models – Logical representation of data – type employee = record name : string; address : string; salary: real; end; – Data independence principle n Physical level: how data is stored Farkas CSCE 824 4
Database Users n Users are differentiated by the way they interact with the system n n Farkas Database Administration: responsible for the structure or schema of the database (DDL), coordinates all activities regarding the database Application programmers – interact with system through DML calls Sophisticated users – form requests in a database query language Naive users – invoke one of the permanent application programs that have been written previously CSCE 824 5
Data Models n A collection of tools for describing – Data – Relationships among data items – Semantics of stored data – Database constraints Entity-Relational Model n UML n Etc. n Farkas CSCE 824 6
Database Management Systems n Smaller and smaller systems – Past: large and expensive DBMS – Present: DBMS in most personal computers n More and more data stored – Past: few MB – Present: terabyte (1012 bytes), petabyte (1015 bytes) n n Farkas Functionality: from physical to view level Optimization CSCE 824 7
Data Definition Language (DDL) Defines the database schema and constraints n DDL compiler data dictionary n Metadata – data about data n Farkas CSCE 824 8
Data Manipulation Language (DML) n n Accessing and manipulating the data Query Languages – Procedural – user specifies what data is required and how to get those data – Nonprocedural – user specifies what data is required without specifying how to get those data Farkas CSCE 824 9
MODELING DATA SEMANTICS Farkas CSCE 824 10
Entity Sets and Relationship Sets n n Farkas Database: collection of entities and relationship among entities Entity: object that exists and distinguishable from other objects Entity set: collection of similar objects Attribute: property of an entity and relationship sets CSCE 824 11
Attributes Domain: set of permitted values for each attributes n Attribute types: n – Simple vs. composite – Single-valued vs. multi-valued – Derived Farkas CSCE 824 12
Example E/R Diagram Name Age Weight Breed Dog Name Boards Owns Kennel Pays License # Address Phone Owner Name Farkas Phone CSCE 824 13
Degree of Relationship Sets n n Number of entity sets participating in a relationship set Binary relationship set: two entity sets (most common) Multiway relationship set: connects more than two entity sets E. g. , An owner frequents certain kennels for certain dogs – Binary relationship can’t represent these requirements – Need 3 -way relationship Farkas CSCE 824 14
Example 3 -Way Diagram Name Age Breed Name Dog Kennel Must Board Weight License # Address Phone Owner Name Farkas Phone CSCE 824 15
Mapping Cardinality n Number of entities to which another entity can be associated via a relationship set – One-one – Many-one (One-many) – Many-many Farkas CSCE 824 16
Roles n n n Entity set may appear more then once in a relationship Label the edges between the relationships and the entity set with names called roles. E. g. , relationships among the dogs: Dog Farkas ancestor descendant CSCE 824 Related to 17
Subclasses in E/R n n Farkas Special case, fewer entities, more properties E. g. , show dog is a dog, but not all dogs are show dogs. It also have properties, type of competition, rank, etc. Assume subclasses form a tree (no multiple inheritance) ISA relationship CSCE 824 18
Example Subclass Name Age Weight Breed Dog Special case, fewer entities, ISA more properties • Subclasses form a tree (no multiple inheritance) Show-Dog • Type Rank Farkas CSCE 824 19
Keys Set of attributes for one entity set such that no two entities in the set has the same value for all the attributes of the key n Each entity set must have a key n Farkas CSCE 824 20
Keys Super key: set of one or more attributes whose value iniquely determine each entity n Candidate key: minimal super key n Primary key: a selected candidate key n Farkas CSCE 824 21
Example Multi-Attribute Key for Owner There are more than One person with the Same name, but they Live at different Places. Owner Name Farkas Address Phone CSCE 824 22
Converting ER Model into Relations Farkas CSCE 824 23
Example Relation Dog Name Pepper Age 10 Weight (lb) Breed Joker 4 83 German Shepherd Mix Bruno null 51 Boxer Farkas 75 CSCE 824 24
Relational Data Model n n n Farkas Set of relation names: R Set of attribute names: A Relation schema: S=(r, {a 1, …, an}) – r relation name in R – {a 1, …, an} subset of A e. g. , (Dog, {Name, Age, Weight, Breed}) CSCE 824 25
Relational Data Model n Tuple over a relation scheme S is a mapping t: {a 1, …, an} dom(a 1 … an) e. g. , t(Dog-Name)=Pepper t(Dog-Age)=10 t(Dog-Weight)= 75 t(Dog-Breed)=German Shepherd Farkas CSCE 824 26
Relational Data Model Relation over schema S is a set of tuples over the scheme n Database: set of relations n Farkas CSCE 824 27
Query Languages n Relational Algebra – Set operations n SQL – Bag operations Farkas CSCE 824 28
Relational Algebra n n n n Farkas Select ( ) Project ( ) Set difference (-) Union ( ) Rename (Px(r)) Set intersection ( ) Natural join ( ) CSCE 824 29
Structured Query Language SQL n Typical SQL query form: SELECT A 1, A 2, . . . , An FROM r 1, r 2, . . . , rm WHERE C – Ais represent attributes to be returned – ris represent relations – C is a condition Farkas CSCE 824 30
Next Class Overview of Information Security (from CSCE 522 slides) Farkas CSCE 824 31
- Slides: 31