1 / 45

Exam 1 Review

Exam 1 Review. Dr. Bernard Chen Ph.D. University of Central Arkansas Fall 2008. Database System. Database: A collection of related data. Data: Known facts that can be recorded and have an implicit meaning. Database Management System (DBMS):

daria
Download Presentation

Exam 1 Review

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Exam 1 Review Dr. Bernard Chen Ph.D. University of Central Arkansas Fall 2008

  2. Database System • Database: A collection of related data. • Data: Known facts that can be recorded and have an implicit meaning. • Database Management System (DBMS): A software package/ system to facilitate the creation and maintenance of a computerized database. • Database System: DBMS + Database

  3. Database V.S. File • In the database approach, a single repository of data is maintained that is defined once then accessed by various users • The major differences between DB and File are: • Self-describing of a DB • Insulation between programs and data • Support of multiple views of the data • Sharing of data and multiuser transaction processing

  4. Self-describing nature of a database system • Database system contains not only the database itself but also a complete definition of the database structure and constrains • The information stored in the catalog is called Meta-data (data about data), and it describes the structure of the primary database.

  5. Categories of data models • High-level or Conceptual data models: Provide concept that are close to the way many users perceive data • Low-level or Physical data model: Provide concepts that describe the details of how data is stored in the computer

  6. Conceptual data models • It uses concepts such as entities, attributes and relationships. • Entity represents a real-world object or concept, such as employee or project • Attribute represents some property of interest that further describes an entity, such as employee’s name or salary • Relation among two or more entities represents an association among two or more entitles

  7. Example of a Database Schema

  8. Schemas and Database State • The data in the database at a particular moment in time is called a database state • The distinction between database schema and database state is very important • When we define a new database, we specify its database schema only to the DBMS • At this point, the corresponding database state is the empty state with no data • We get the initial state of the database when the database is first loaded • From then on, every time an update operation is applied to the database, we get another database state

  9. Three-Schema Architecture • Defines DBMS schemas at three levels: • Internal schema at the internal level to describe physical storage structures and access paths (e.g indexes). • Conceptual schema at the conceptual level to describe the structure and constraints for the whole database for a community of users. • External schemas at the external level to describe the various user views.

  10. The three-schema architecture

  11. Centralized DBMS Architecture • A centralized DBMS in which all the DBMS functionality, application program execution, and user interface processing were carried out on a single machine • The client/server architecture was developed to deal with computer environment in which a large number of PCs, workstation, file server… • This is called two-tire architectures because the software components are distributed over two systems: client and server • The emergence of the Web changed the roles of client and server, leading to the three-tier architecture

  12. A Physical Centralized Architecture

  13. Logical two-tier client server architecture

  14. Three-tier client-server architecture

  15. Entities and Attributes • The most basic object that the ER model represents is an entity • An entity maybe an object with a physical existence (a person, a car, house…) or it maybe an object with conceptual existence (a company, a job, or a course) • Each entity has Attributes --- the particular properties that describe it

  16. Attributes • Several types of attribute occur in the ER model • Simple vs. Composite • Single value vs. Multi-value • Stored vs. Derived

  17. Composite vs. Simple Attributes • Composite attributes can be divided into smaller subparts. • For example: Address attribute of the EMPLOYEE entity can be further subdivided into street_address, city, state, zip_code • Simple attributes can not be further divisible • For example, street_address can be subdivided into Number, street, and apt# • The value of composite attribute is the concatenation of the values of its constituent simple attributes

  18. Single value vs. Multi-value • Most attributes have a single value for a particular entity; such attribute are called single-valued • In some cases an attribute can have a set of value for the same entity --- for example, colors attribute for a car, or a college_degree for a person • Such attributes are called multivalued • A multivalued attribute may have lower and upper bonds to constrain the number of values allowed for each entity

  19. Stored vs. Derived • In some cases, two (or more) attribute calues are related --- for example, the Age and Birth_date of a person • The Age attribute is called a derived attribute and is said to be derived from the Birth_date attribute, which is called a stored value

  20. Key Attributes • An important constrain on the entities of an entity type is the KEY on attributes • An attribute of an entity type for which each entity must have a unique value is called a key attribute of the entity type. • For example, SSN of EMPLOYEE.

  21. In some cases, the same entity type participates more than once in a relationship type in different roles Example Employee and supervised Recursive Relationship

  22. Relationship example • Consider a relationship type work_for between the two entities type EMPLOYEE and DEPARTMENT • Each relationship instance in the relationship set associates one EMPLOYEE entity and one DEPARTMENT entity

  23. Relationship between EMPLOYEE and DEPARTMENT

  24. Constrains on Relationship types • Sometimes if we want to describe “each employee must work for exactly one department”, then we would like to describe this constrain in the schema • The cardinality ratio for a binary relationship specifies the max number of relationship instances that an entity can participate in. • For example---in the Works_for binary relationship, DEPARTMENT:EMPLOYEE is of cardinality ration 1:N, meaning each department can be related to any number of employees, but an employee can only be related to one department

  25. Relationship between EMPLOYEE and DEPARTMENT

  26. Relationship between EMPLOYEE and DEPARTMENT

  27. Relationship between EMPLOYEE and DEPARTMENT

  28. cardinality ratio • The possible cardinality ratio for binary relationships are 1:1, 1:N, N:1, M:N • Example: • 1:1 Manages relationship between employee and department • M:N an employee can work on several projects and a project can have several employees

  29. Participation Constrain • The participation constrain specifies whether the existence of an entity depends on its being related to another entity via the relationship type • There are two types of participation constrains: • Total • Partial

  30. Participation Constrain • For example • If a company policy states that every employee must work for a department, then it’s total • Not every Employee is a Manager, so this relationship is partial

  31. Relationship between EMPLOYEE and DEPARTMENT

  32. ER DIAGRAM

  33. Weak Entity Types • Entity types do not have key attribute of their own are called weak entity types • In contrast, regular entity types that do have key attribute are called strong entity types • A weak entity type normally has a partial key, which is the set of attributes that can uniquely identify weak entities that are related to the same owner entity

  34. Weak Entity Type • Weak entity types can sometimes be represented as complex attributes • Complex Attributes: combination of composite and multi-valued attributes • In the example, we could specify a multi-valued attribute Dependents for EMPLOYEE, which is a composite attribute with component attributes Name, Birthday, Sex and Relationship

  35. Subclasses, Superclasses and Inheritance • We call each of these subgroupings a subclass of the EMPLOYEE entity type, and the EMPLOYEE entity type is called the superclass for each of these subclasses. • These are called superclass/subclass (as well as simply class/subclass) relationships: • EMPLOYEE/SECRETARY • EMPLOYEE/TECHNICIAN • EMPLOYEE/MANAGER • … • These are also called IS-A relationships • SECRETARY IS-A EMPLOYEE, TECHNICIAN IS-A EMPLOYEE, ….

  36. Subclasses and Superclasses

  37. Subclasses, Superclasses and Inheritance • An important concept associated with subclasses is that of type inheritance • An entity that is member of a subclass inherits • All attributes of the entity as a member of the superclass • All relationships of the entity as a member of the superclass

  38. Constraints on Specialization and Generalization • Two basic constraints can apply to a specialization/generalization: • Disjointness Constraint: Specifies that the subclasses of the specialization must be disjoint: an entity can be a member of at most one of the subclasses of the specialization • Completeness Constraint: If not disjoint, specialization is overlapping

  39. Displaying an attribute-defined specialization in EER diagrams

  40. Example of overlapping total Specialization

  41. Constraints on Specialization and Generalization • Completeness Constraint: • Total specifies that every entity in the superclass must be a member of some subclass in the specialization/generalization • Shown in EER diagrams by a double line • Partial allows an entity not to belong to any of the subclasses • Shown in EER diagrams by a single line • In general, a superclass that was identified through the generalization process usually total, because the superclass is derived from the subclasses and hence contains only the entities that are in the subclass

  42. Specialization/Generalization Hierarchies, Lattices • A subclass may itself have further subclasses specified on it • Hierarchy has a constraint that every subclass has only one superclass (called single inheritance); this is basically a tree structure • In a lattice, a subclass can be subclass of more than one superclass (called multiple inheritance)

  43. Union • All of the superclass/subclass relationships we have seen so far origin from a single superclass • Sometimes we may need more than one superclass • In this case, the subclass will represent a collection of objects that is a subset of the UNION of distinct entity types • We call such a subclass a UNION TYPE

  44. UNION • Attribute inheritance works more selectively in the case of UNION. • For example, OWNER entity inherits attributes of a COMPANY, a PERSON ORa BANK • A shared subclass such as ENGINEERING_MANAGER inherits ALL the attributes of its superclasses

  45. Two categories (UNION types): OWNER, REGISTERED_VEHICLE

More Related