Exam 1 Review

1
Exam 1 Review

• Dr. Bernard Chen Ph.D.
• University of Central Arkansas

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
  employees 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
Relationship

• The initial design is typically not complete
• Refining the initial design by introducing
  relationships
• ER model has three main concepts
• Entities (and their entity types and entity sets)
• Attributes (simple, composite, multivalued)
• Relationships (and their relationship types and
  relationship sets)

22
Recursive Relationship

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

23
ER DIAGRAM
24
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

25
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

26
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, .

27
Subclasses and Superclasses
28
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

29
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

30
Displaying an attribute-defined specialization in
EER diagrams
31
Example of overlapping total Specialization
32
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

33
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)

34
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

35
UNION

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

36
Two categories (UNION types) OWNER,
REGISTERED_VEHICLE