csce824-lecture2

csce824-lecture2 - Introduction Introduction Database...

Info iconThis preview shows page 1. Sign up to view the full content.

View Full Document Right Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: Introduction Introduction Database Management Systems Database Management System (DBMS) Collection of – interrelated data and – set of programs to access the data Convenient and efficient processing of data Database Application Software Farkas Farkas CSCE 824 ­ Spring 2011 2 Evolution of Database Systems Farkas 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 ­ Spring 2011 3 Abstraction View level: different perspectives – application programs hide irrelevant data Logical level: data models – Logical representation of data – Different approaches: relational, hierarchical, network, object oriented, semi­structured, etc. – Data independence principle Farkas Farkas Physical level: how data is stored CSCE 824 ­ Spring 2011 4 Data Models A collection of tools for describing – – – – Farkas Farkas Data Relationships among data items Semantics of stored data Database constraints Entity­Relational Model UML Etc. CSCE 824 ­ Spring 2011 5 Database Management Systems Smaller and smaller systems – – Past: large and expensive DBMS Present: DBMS in most personal computers More and more data stored – Past: few MB – Present: terabyte (1012 bytes), petabyte (1015 bytes) Farkas Farkas Functionality: from physical to view level Optimization CSCE 824 ­ Spring 2011 6 Data Definition Language (DDL) Defines the database schema and constraints DDL compiler data dictionary Metadata – data about data Farkas Farkas CSCE 824 ­ Spring 2011 7 Data Manipulation Language (DML) 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 Farkas CSCE 824 ­ Spring 2011 8 MODELING DATA SEMANTICS MODELING DATA SEMANTICS Farkas CSCE 824 ­ Spring 2011 9 Entity Sets and Relationship Sets 12/13/11 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 520 10 Attributes Domain: set of permitted values for each attributes Attribute types: – – – 12/13/11 Simple vs. composite Single­valued vs. multi­valued Derived CSCE 520 11 Example E/R Diagram Name Age Weight Breed Dog Name Boards Owns Kennel Pays License # Address Phone Owner 12/13/11 12/13/11 Name CSCE 520 Phone 12 Degree of Relationship Sets 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 12/13/11 CSCE 520 13 Example 3­Way Diagram Name Age Breed Name Dog Kennel Must Board Weight License # Address Phone Owner 12/13/11 12/13/11 Name CSCE 520 Phone 14 Mapping Cardinality Number of entities to which another entity can be associated via a relationship set – – – 12/13/11 One­one Many­one (One­many) Many­many CSCE 520 15 Roles 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 12/13/11 ancestor descendant CSCE 520 Related to 16 Subclasses in E/R 12/13/11 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 520 17 Example Subclass Name Age Breed Weight Dog • Special case, fewer entities, more properties • Subclasses form a tree (no multiple inheritance) ISA Show­Dog Type Rank 12/13/11 12/13/11 CSCE 520 18 Keys 12/13/11 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 Each entity set must have a key CSCE 520 19 Keys 12/13/11 Super key: set of one or more attributes whose value iniquely determine each entity Candidate key: minimal super key Primary key: a selected candidate key CSCE 520 20 Example Multi­Attribute Key for Owner There are more than One person with the Same name, but they Live at different Places. Owner Name 12/13/11 12/13/11 Address Phone CSCE 520 21 Design Requirements Avoid redundancy Limit use of weak entity sets Don’t use entity set when can use attribute Binary vs. multi­way relationship sets 12/13/11 CSCE 520 22 Converting ER Model into Converting ER Model into Relations Farkas CSCE 824 ­ Spring 2011 23 Example Relation Dog Name Age Pepper 10 Joker 4 83 German Shepherd Mix Bruno null 51 Boxer Farkas Farkas Weight (lb) 75 CSCE 824 ­ Spring 2011 Breed 24 Relational Data Model Farkas Farkas Set of relation names: R Set of attribute names: A Relation schema: S=(r,{a1, …,an}) – r relation name in R – {a1, …,an} subset of A e.g., (Dog,{Name, Age, Weight, Breed}) CSCE 824 ­ Spring 2011 25 Relational Data Model Tuple over a relation scheme S is a mapping t: {a1, …,an} dom(a1 ∪ … ∪ an) e.g., Farkas Farkas t(Dog­Name)=Pepper t(Dog­Age)=10 t(Dog­Weight)= 75 t(Dog­Breed)=German Shepherd CSCE 824 ­ Spring 2011 26 Relational Data Model Farkas Farkas Relation over schema S is a set of tuples over the scheme Database: set of relations CSCE 824 ­ Spring 2011 27 Query Languages Relational Algebra – Set operations SQL – Bag operations Farkas Farkas CSCE 824 ­ Spring 2011 28 Relational Algebra Farkas Farkas Select (σ) Project (Π) Set difference (­) Union (∪) Rename (Px(r)) Set intersection (∩) Natural join (⊗) CSCE 824 ­ Spring 2011 29 Structured Query Language SQL Typical SQL query form: SELECT A1, A2, ..., An FROM r1, r2, ..., rm WHERE C – Ais represent attributes to be returned – ris represent relations – C is a condition Farkas Farkas CSCE 824 ­ Spring 2011 30 Next Class Overview of Information Security (from CSCE 522 slides) Farkas Farkas CSCE 824 ­ Spring 2011 31 ...
View Full Document

Ask a homework question - tutors are online