Read through the whole project before beginning. Based on the information provided below, you will be creating Database Design Language and Entity Relationship Diagram.Database Design Language (DBDL)Use Word to create the DBDL. When creating the DBDL, list out all relations in 3NF. On each relation, create functional dependencies to make sure all relations are in 3NF!!Entity Relationship Diagram (ERD) An ERD is the visually representation of the database structure. Draw the diagram for the scenario using LucidChart. When drawing the objects, be sure to use the correct shapes and syntax. Example: Entities are a rectangle box, the Entity name is a singular noun, primary keys are underline, etc. Each relationship must be show to be either 1:1 or 1:M. There will be no M:M relationships by creating all relations in 3NF.List out all alternate keys, secondary keys, and foreign keys under the relation using the correct syntax. Do not forget to put your name on the documents. Submit the files via Canvas. Be sure to submit as a PDF or Word Document. If you use Visio, you must export the file as a PDF. Do not submit .VSD files.An online movie database keeps tracks of information on movies, actors, directors, the genre of movies, and the characters portrayed by each actor in the movie. The specific data requirements for the database is summarized as follows:Each movie is identified by a unique number. Other information kept on each movie is the name of the movie, length of the movie, release date and parental rating.An actor is identified by a unique number, first name, last name, date of birth and gender. A director is identified by a unique number, first name, last name, date of birth and gender.A character is identified by a unique number, first name, and last name. A genre is identified by a unique number and the description of the genre.A Movie has multiple actors (or none at all).An Actor can play multiple roles in a movie.A character must be associated with a movie and an actor. A character can be in multiple movies. (i.e. Sequels)A character can be portrayed by multiple actors in a movie. (i.e. Character in a movie has a younger and older version – two actors need to play the part).A Movie can have multiple directors (or none at all).A movie must have at least one genre (or could have many).Chapter 6
Database Design 2: Design Method
Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not
be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
Objectives (1 of 2)
• After completing this chapter, you will be able to:
• Discuss the general process and goals of database design
• Define user views and explain their function
• Use Database Design Language (DBDL) to document database
designs
• Create an entity-relationship (E-R) diagram to represent a
database design visually
• Present a method for database design at the information level and
view examples illustrating this method
• Explain the physical-level design process
• Discuss top-down and bottom-up approaches to database design
and examine the advantages and disadvantages of both methods
• Use a survey form to obtain information from users prior to
beginning the database design process
Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be
scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
Objectives (2 of 2)
• Review existing documents to obtain information prior to beginning
the database design
• Discuss special issues related to implementing one-to-one
relationships and many-to-many relationships involving more than
two entities
• Identify entity subtypes and their relationships to nulls
• Learn how to avoid potential problems when merging third normal
form relations
• Examine the entity-relationship model for representing and
designing databases
Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be
scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
Introduction
• Two-step process for database design
• Information-level design: completed independently of any
particular DBMS
• Design a database that satisfies the organizations requirements as
clearly as possible
• Independently of any particular DBMS that the organization will
ultimately use
• Designers must consider the characteristics of the particular DBMS
that the organization will use
• Physical-level design: information-level design adapted for the
specific DBMS that will be used
• Must consider characteristics of the particular DBMS
Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be
scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
User Views
• User view: set of requirements necessary to support
operations of a particular database user
• Logical way of looking at the database setup to support the
activities of a user or a group of users
• Cumulative design: supports all user views encountered
during design process
• Working on individual user views is usually more manageable than
attempting to turn the design of the entire database into one large
task
Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be
scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
Information-Level Design Method
• For each user view:




Represent the user view as a collection of tables
Normalize these tables
Identify all keys in these tables
Merge the result of steps into the cumulative design
Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be
scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
Step 1: Represent the User View as a Collection of
Tables
• Step 1a: determine the entities involved and create a
separate table for each type of entity
• Step 1b: establish the primary key for each table
• Step 1c: define properties for each entity
• Step 1d: form relationships between the entities
• One-to-many: include primary key of the “one” table as a foreign
key in the “many” table
• Many-to-many: create a new table whose primary key is the
combination of the primary keys of the original tables
• One-to-one: simplest implementation is to treat it as a one-to-many
relationship
Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be
scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
Step 1: Represent the User View as a Collection of
Tables
• Step 1a: determine the entities involved and create a
separate table for each type of entity
• Create a database to keep track of employee and their department
• Entities needed are Employee and Department
• Step 1b: establish the primary key for each table
• EmployeeID for Employee
• DepartmentID for Department
• Step 1c: define properties for each entity
• LastName, FirstName, Street, City, State, Zip, Wage and SSN for
Employee
• DepartmentName, and Location for Department
Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be
scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
Step 1: Represent the User View as a Collection of
Tables
• Step 1d: form relationships between the entities
• How to tell which department an employee is in?
• Place DeptID in Employee
• Employee(EmployeeID, LastName, FirstName, Street,
City, State, Zip, Wage, SSN, DeptNum)
• Department(DepartmentID, DepartmentName, Location)
Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be
scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
Step 2: Normalize the Tables
• Normalize each table
• Target is third normal form
• Careful planning in early phases of the process usually rules out
need to consider fourth normal form
Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be
scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
Step 3: Identify All Keys
• For each table, identify:
• Primary key: natural, artificial, or surrogate
• Alternate keys: column(s) that could have been chosen as a
primary key but was not
• Secondary keys: columns of interest strictly for retrieval purposes
• Foreign keys: column(s) in one table that is required to match
value of the primary key for some row in another table or is
required to be null
• Create relationships between tables
• Enforce certain types of integrity constraints
Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be
scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
Types of Primary Keys
• Natural (i.e., logical key or an intelligent) key: consists of
a column that uniquely identifies an entity
• SSN, VIN, UPC, ISBN
• Artificial key: column created for an entity to serve solely
as the primary key and that is visible to users
• Student Number
• Surrogate (i.e., synthetic) key: system-generated; usually
hidden from users
• Autogenerated number – DeptNum
Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be
scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
Database Design Language (DBDL) (1 of 3)
• Table name followed by columns in parentheses
• Primary key column(s) underlined
• AK identifies alternate keys
• SK identifies secondary keys
• FK identifies foreign keys
• column(s) in one table that is required to match value of the
primary key for some row in another table or is required to be null
• Foreign keys followed by an arrow pointing to the table identified
by the foreign key
• Used to create relationships between tables
• Used to enforce certain types of integrity constraints
Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be
scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
Database Design Language (DBDL) (2 of 3)
Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be
scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
Database Design Language (DBDL) (3 of 3)
Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not
be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
Entity-Relationship (E-R) Diagrams (1 of 2)
• Visually represents database structure
• Rectangle represents each entity; entity’s name appears above the
rectangle
• Primary key for each entity appears above the line in the entity’s
rectangle
• Other columns of entity appear below the line in rectangle
• Letters AK, SK, and FK appear in parentheses following the
alternate key, secondary key, and foreign key
• For each foreign key, a dotted line leads from the rectangle for the
table being identified to the rectangle for the table containing the
foreign key
Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be
scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
Entity-Relationship (E-R) Diagrams (2 of 2)
Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be
scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
ER Model Constructs
• Entities:
• Entity instance–person, place, object, event, concept (often
corresponds to a row in a table)
• Entity Type–collection of entities (often corresponds to a table)
• Relationships:
• Relationship instance–link between entities (corresponds to
primary key-foreign key equivalencies in related tables)
• Relationship type–category of relationship…link between entity
types
• Attribute–property or characteristic of an entity or relationship type
(often corresponds to a field in a table)
Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be
scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
ER Model Constructs
• Associative entity: exists to implement a many-to-many
relationship
• Existence dependency: existence of one entity depends
on the existence of another related entity
• Weak entity: depends on another entity for its own
existence
Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be
scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
Step 4: Merge the Result into the Design (1 of 2)
• Combine tables that have the same primary key to form a
new table




Primary key is same as the primary key in the tables combined
Contains all the columns from the tables combined
If duplicate columns, remove all but one copy of the column
Make sure new design is in third normal form
Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be
scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
Step 4: Merge the Result into the Design (2 of 2)
Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be
scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
Database Design Examples (1 of 8)
• Information-level design for BITS database: stores
information about consultants, clients, tasks, and work
orders
• User view requirements
• Constraints
Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be
scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
Database Design Examples (2 of 8)
Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be
scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
Database Design Examples (3 of 8)
Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be
scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
Database Design Examples (4 of 8)
Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be
scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
Database Design Examples (5 of 8)
• Information-level design for Henry Books database:
information about branches, publishers, authors, and
books
• User view requirements
Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be
scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
Database Design Examples (6 of 8)
Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be
scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
Database Design Examples (7 of 8)
Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be
scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
Database Design Examples (8 of 8)
Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be
scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
Physical-Level Design
• Undertaken after information-level design completion
• Most DBMSs support primary, candidate, secondary, and foreign
keys
• To enforce restrictions, programmers must include logic in their
programs
Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be
scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
Top-Down Versus Bottom-Up Design
• Bottom-up design method
• Specific user requirements are synthesized into a design
• Top-down design method
• Begins with general database that models overall enterprise
• Refines model until design supports all necessary applications
Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be
scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
Survey Form
• Used to collect information from users
• Must contain particular elements





Entity information
Attribute (column) information
Relationships
Functional dependencies
Processing information
Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be
scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
Obtaining Information from Existing Documents
(1 of 4)
• Existing documents can furnish information about
database design
• Identify and list all columns and give them appropriate names
• Distinguish functional dependencies
• Determine the tables and assign columns
Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be
scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
Obtaining Information from Existing Documents
(2 of 4)
Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be
scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
Obtaining Information from Existing Documents
(3 of 4)
Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be
scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
Obtaining Information from Existing Documents
(4 of 4)
Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be
scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
One-to-One Relationship Considerations (1 of 2)
• Include the primary key of each table as a foreign key in
the other table
• No guarantee that the information will match; may create update
anomalies and inconsistency in the database
• One solution: create a single table
• Workable, but not the best solution
• Better solution
• Create separate tables for clients and consultants and include the
primary key of one of them as a foreign key in the other
Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be
scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
One-to-One Relationship Considerations (2 of 2)
Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be
scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
Many-to-Many Relationship Considerations (1 of 3)
• Complex issues arise when more than two entities are
related in a many-to-many relationship
• Many-to-many-to-many relationships involve multiple entities
• Crucial issue: independence
• Deciding between a single many-to-many-to-many relationship
and two (or three) many-to-many relationships
Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be
scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
Many-to-Many Relationship Considerations (2 of 3)
Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be
scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
Many-to-Many Relationship Considerations (3 of 3)
Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be
scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
Relationship Types
• One-to-One (1:1)
– Each entity in the relationship will have exactly one related entity
• One-to-Many (1:N)
– An entity on one side of the relationship can have many related
entities, but an entity on the other side will have a maximum of one
related entity
• Many-to-Many (M:N)
– Entities on both sides of the relationship can have many related
entities on the other side
Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be
scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
Nulls and Entity Subtypes (1 of 5)
• A null is a special value
• Represents absence of a value in a field
• Used when a value is unknown or inapplicable
• Avoided by creating separate tables
• Entity subtype: table that is a subtype of another table
• Complete categories: all records fall into the categories
• Incomplete category: not all records fall into the category
Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be
scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
Supertype and Subtype
• Subtype: A subgrouping of the entities in an entity type
that has attributes distinct from those in other
subgroupings
• Supertype: A generic entity type that has a relationship
with one or more subtypes
• Attribute Inheritance:
– Subtype entities inherit values of all attributes of the supertype
– An instance of a subtype is also an instance of the supertype
Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be
scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
Nulls and Entity Subtypes (2 of 5)
Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be
scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
Nulls and Entity Subtypes (3 of 5)
Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be
scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
Nulls and Entity Subtypes (4 of 5)
Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be
scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
Nulls and Entity Subtypes (5 of 5)
Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be
scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
Avoiding Problems with Third Normal Form When
Merging Tables
• When combining third normal form tables, the result
might not be in third normal form
• Convert it to third normal form before proceeding to the next user
view
• Be cautious when representing user views
• Always attempt to determine whether determinants exist and
include them in tables
Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be
scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
The Entity-Relationship Model (1 of 3)
• Approach to representing data in a database
• Entities are drawn as rectangles and relationships are drawn as
diamonds, with lines connecting the entities involved in
relationships
• Composite entity: relationship between other entities
• Existence dependency: existence of one entity depends on the
existence of another related entity
• Weak entity: depends on another entity for its own existence
• Cardinality: number of items that must be included in a relationship
• Maximum or minimum
Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be
scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
The Entity-Relationship Model (2 of 3)
Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be
scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
The Entity-Relationship Model (3 of 3)
Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be
scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
Summary (1 of 4)
• Database design is a two-part process: information-level
design (not dependent on a particular DBMS) and
physical-level design (appropriate for the particular
DBMS being used)
• User view: set of necessary requirements to support a
particular user’s operations
• Information-level design steps for each user view:
represent the user view as a collection of tables,
normalize these tables, represent all keys (primary,
alternate, secondary, and foreign), and merge the results
into the cumulative design
Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be
scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
Summary (2 of 4)
• Database design is represented in Database Design
Language (DBDL)
• Designs can be represented visually using entityrelationship (E-R) diagrams
• Physical-level design process consists of creating a table
for each entity in the DBDL design
• Design method presented in this chapter is bottom-up
• Survey form is useful for documenting the information
gathered for database design process
Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be
scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
Summary (3 of 4)
• To obtain information from existing documents, list all
attributes present in the documents, identify potential
functional dependencies, make a tentative list of tables,
and use the functional dependencies to refine the list
• To implement a one-to-one relationship, include primary
key of one table in the other table as a foreign key and
indicate the foreign key as an alternate key
Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be
scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
Summary (4 of 4)
• If a table’s primary key consists of three (or more)
columns, determine whether there are independent
relationships between pairs of these columns
• If a table contains columns that can be null and the nulls
mean that the column is inapplicable for some rows, you
can split the table, placing the null column(s) in separate
tables
• The result of merging third normal form tables may not be
in third normal form
• The entity-relationship (E-R) model is another method of
representing the structure of a database using a
conceptual diagram
Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be
scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
Chapter 7
DBMS Functions
Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not
be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
Objectives
• After completing this chapter, you will be able to:










Introduce the functions, or services, provided by a DBMS
Describe how a DBMS handles updating and retrieving data
Examine the catalog feature of a DBMS
Illustrate the concurrent update problem and describe how a
DBMS handles this problem
Explain the data recovery process in a database environment
Describe the security services provided by a DBMS
Examine the data integrity features provided by a DBMS
Discuss the extent to which a DBMS achieves data independence
Define and describe data replication
Present the utility services provided by a DBMS
Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be
scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
Introduction
• Functions of a DBMS









Update and retrieve data
Provide catalog services
Support concurrent update
Recover data
Provide security services
Provide data integrity features
Support data independence
Support data replication
Provide utility services
Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be
scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
Update and Retrieve Data (1 of 4)
• Fundamental capability of a DBMS
• Users don’t need to know how data is physically structured on a
storage medium or which processes the DBMS uses to manipulate
the data
• Users add, change, and delete existing records during updates
Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be
scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
Update and Retrieve Data (2 of 4)
Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be
scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
Update and Retrieve Data (3 of 4)
Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be
scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
Update and Retrieve Data (4 of 4)
Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be
scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
Provide Catalog Services
• Metadata: information about the data in a database
• Includes table descriptions and field definitions
• Catalog stores metadata and makes it accessible to
users
• Enterprise DBMSs often have a data dictionary
• A super catalog
Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be
scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
Support Concurrent Update
• DBMS must ensure accuracy when several users update
database at the same time
• Concurrent update: multiple users make updates to the same
database at the same time
Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be
scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
The Concurrent Update Problem (1 of 4)
Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be
scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
The Concurrent Update Problem (2 of 4)
Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be
scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
The Concurrent Update Problem (3 of 4)
Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be
scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
The Concurrent Update Problem (4 of 4)
Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be
scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
Avoiding the Lost Update Problem (1 of 2)
• Batch processing
• All updates done through a special program
• Problem: data becomes out of date
• Does not work in situations that require data to be current
Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be
scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
Avoiding the Lost Update Problem (2 of 2)
Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be
scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
Two-Phase Locking (1 of 3)
• Locking: deny other users access to data while one
user’s updates are being processed
• Transaction: set of steps completed by a DBMS to
accomplish a single user task
• Two-phase locking solves lost update problem
• Growing phase: DBMS locks more rows and releases none of the
locks
• Shrinking phase: DBMS releases all the locks and acquires no
new locks
Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be
scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
Two-Phase Locking (2 of 3)
Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be
scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
Two-Phase Locking (3 of 3)
Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be
scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
Deadlock (1 of 2)
• Deadlock or deadly embrace
• Two users hold a lock and require a lock on the resource that the
other already has
• To minimize occurrence, make sure all programs lock records are
in the same order whenever possible
• Managing deadlocks
• DBMS detects and breaks any deadlock
• DBMS chooses one user to be the victim
Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be
scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
Deadlock (2 of 2)
Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be
scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
Locking on PC-Based DBMSs
• Usually more limited than locking facilities on enterprise
DBMSs
• Additional burden on the programmers who write the
• programs that allow concurrent update
• Fairly typical facilities
• Programs can lock an entire table or an individual row within a
table, but only one or the other
• Programs can release any or all of the locks they currently hold
• Programs can inquire whether a given row or table is locked
Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be
scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
Timestamping
• DBMS assigns each database update a unique time (i.e.,
timestamp) when the update started
• Advantages
• Avoids need to lock rows
• Eliminates processing time needed to apply and release locks and
to detect and resolve deadlocks
• Disadvantages
• Additional storage and memory space
• Extra processing time
Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be
scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
Recover Data
• Recovery: returning database to a correct state from an
incorrect state
• Simplest recovery involves using backups
• Backup or save: copy of database
Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be
scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
Journaling (1 of 3)
• Journaling: maintaining a journal or log of all updates
• Log is available even if database is destroyed
• Information kept in log for each transaction






Transaction ID
Date and time of each update
Before image
After image
Start of a transaction entry
Successful completion (commit) of a transaction
Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be
scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
Journaling (2 of 3)
Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be
scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
Journaling (3 of 3)
Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be
scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
Forward Recovery (1 of 2)
• DBA executes a DBMS recovery program
• Recovery program applies after images of committed transactions
from log to database
• Improve the performance of the recovery program
• First scan the log and then apply the last after image
Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be
scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
Forward Recovery (2 of 2)
Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be
scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
Backward Recovery (1 of 2)
• Database not in a valid state
• Transactions stopped in midstream
• Incorrect transactions
• Backward recovery or rollback
• Undo problem transactions
• Apply before images from log to undo their updates
Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be
scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
Backward Recovery (2 of 2)
Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be
scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
Recovery on PC-Based DBMSs
• PC-based DBMSs generally do not offer sophisticated
recovery features
• Most provide users with a simple way to make backup copies and
to recover the database later by copying the backup over the
database
• Regularly make backup copies using DBMS
• Use most recent backup for recovery
• Systems with large number of updates between backups
• Recovery features not supplied by DBMS need to be included in
application programs
Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be
scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
Provide Security Services
• Security: prevention of unauthorized access, either
intentional or accidental, to a database
• Most common security features used by DBMSs





Encryption
Authentication
Authorizations
Views
Privacy
Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be
scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
Encryption
• Encryption converts data to a format indecipherable to
another program and stores it in an encrypted format
• Encryption process is transparent to a legitimate user
• Decrypting: reversing the encryption
• In Access, encrypt a database with a password
Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be
scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
Authentication (1 of 2)
• Authentication refers to techniques for identifying the
person attempting to access the DBMS
• Password: string of characters assigned by DBA to a user that
must be entered for access
• Biometrics: identify users by physical characteristics such as
fingerprints, voiceprints, handwritten signatures, and facial
characteristics
• Smart cards: small plastic cards with built-in circuits containing
processing logic to identify the cardholder
Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be
scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
Authentication (2 of 2)
Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be
scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
Authorizations
• DBA can use authorization rules to specify which users
have what type of access to which data
• Permissions: specify what kind of access the user has to objects in
the database
• Workgroups: groups of users
Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be
scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
Views
• Snapshots of certain data in the database at a given
moment in time
• Can be used for security purposes
• Tables or fields to which the user does not have access in his or
her view effectively do not exist for that user
Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be
scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
Privacy
• Privacy: right of individuals to have certain information
about them kept confidential
• Laws and regulations dictate some privacy rules
• Organizations institute additional privacy rules
Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be
scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
Provide Data Integrity Features (1 of 3)
• Rules followed to ensure data is accurately and
consistently updated
• Key integrity
• Foreign key and primary key constraints
• Data integrity
• Data type
• Legal values
• Format
Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be
scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
Provide Data Integrity Features (2 of 3)
• Four ways of handling integrity constraints:




Constraint is ignored
Responsibility for constraint enforcement placed on users
Responsibility for constraint enforcement placed on programmers
Responsibility for constraint enforcement placed on DBMS
Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be
scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
Provide Data Integrity Features (3 of 3)
Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be
scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
Support Data Independence
• Data independence: can change database structure
without needing to change programs that access the
database
• Types of changes:




Adding a field
Changing the field length
Creating an index
Adding or changing a relationship
Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be
scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
Adding a Field
• No need to change any program except those programs
using the new field
• SQL SELECT * FROM command will present an extra
field
• Solution: list the required fields in an SQL SELECT command
instead of using *
Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be
scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
Changing the Length of a Field
• Generally, no need to change programs
• Need to change the program if:
• Certain portion of screen or report is set aside for the field and the
space cannot fit the new length
Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be
scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
Creating an Index
• To create an index, enter a simple SQL command or
select a few options
• Most DBMSs use the new index automatically
• For some DBMSs, you may need to make minor changes in
already existing programs
Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be
scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
Adding or Changing a Relationship
• Trickiest of all
• May need to restructure database
Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be
scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
Support Data Replication (1 of 2)
• Replicated data: duplicated data
• Manage multiple copies of same data in multiple locations
• Maintained for performance or other reasons
• Ease of access and portability
• Replicas: copies
• Synchronization: DBMS exchanges all updated data
between master database and a replica
Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be
scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
Support Data Replication (2 of 2)
Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be
scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
Provide Utility Services
• Utility services assist in general database maintenance





Change database structure
Add new indexes and delete indexes
Use services available from operating system
Export and import data
Support for easy-to-use edit and query capabilities, screen
generators, report generators, etc.
• Support for procedural and nonprocedural languages
• Procedural language: must tell computer precisely how a given task is
to be accomplished
• Nonprocedural language: describe task you want computer to
accomplish
• Easy-to-use menu-driven or switchboard-driven interface
Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be
scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
Summary (1 of 3)
• DBMS allows users to update and retrieve data in a
database without needing to know how data is structured
on disk or manipulated
• DBMS must store metadata (data about the data) and
make this data accessible to users
• DBMS must support concurrent update
• Locking denies access by other users to data while
DBMS processes one user’s updates
• During deadlock, or deadly embrace, two or more users
are waiting for the other user to release a lock before
they can proceed
Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be
scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
Summary (2 of 3)
• In timestamping, DBMS processes updates to a
database in timestamp order
• DBMS must provide methods to recover a database in
the event the database is damaged
• Enterprise DBMSs maintain a log or journal of all
database updates since the last backup; log is used in
recovery process
• DBMSs provide security features (e.g., encryption,
authentication, authorizations, and views) to prevent
unauthorized access to a database
Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be
scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.
Summary (3 of 3)
• DBMS must follow rules or integrity constraints (key
integrity constraints and data integrity constraints) so that
it updates data accurately and consistently
• DBMS must support data independence
• DBMS must have facility to handle data replication
• DBMS must provide utility services that assist in general
maintenance of a database
Starks/Pratt/Last, Concepts of DB Management, 9th Edition. © 2019 Cengage. All Rights Reserved. May not be
scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.

Purchase answer to see full
attachment




Why Choose Us

  • 100% non-plagiarized Papers
  • 24/7 /365 Service Available
  • Affordable Prices
  • Any Paper, Urgency, and Subject
  • Will complete your papers in 6 hours
  • On-time Delivery
  • Money-back and Privacy guarantees
  • Unlimited Amendments upon request
  • Satisfaction guarantee

How it Works

  • Click on the “Place Order” tab at the top menu or “Order Now” icon at the bottom and a new page will appear with an order form to be filled.
  • Fill in your paper’s requirements in the "PAPER DETAILS" section.
  • Fill in your paper’s academic level, deadline, and the required number of pages from the drop-down menus.
  • Click “CREATE ACCOUNT & SIGN IN” to enter your registration details and get an account with us for record-keeping and then, click on “PROCEED TO CHECKOUT” at the bottom of the page.
  • From there, the payment sections will show, follow the guided payment process and your order will be available for our writing team to work on it.