DATABASE
MANAGEMENT
SYSTEMS - PROJECT
Introduction
Intro. To DBMS 1
Sit with your Project Group
⚫ Overview
⚫ Checkpoints
⚫ Grading
⚫ Worksheets
Intro. To DBMS 2
Project Assignment
Intro. To DBMS 3
Project Deliverables
Intro. To DBMS 4
Project Deliverables
Intro. To DBMS 5
Part I – The Final Report
◦ Your final document will include a relational database schema, entity relationship diagram, SQL queries, and reports, as indicated below.
Part I, Section 1 - Database Description
A database description document that contains
◦ ER Model
◦ Relational Schema
◦ Normalization
◦ Indexes
◦ Views
◦ Transactions
Intro. To DBMS 6
Part I, Section 2 - User Manual
Describe the usage of your
database, for use by developers who are going to be writing
code to use your database.
◦Tables
◦Sample SQL
Intro. To DBMS 7
Part I, Section 3 - Graded Checkpoint Documents
All of your original, graded checkpoint documents organized in a neat and
professional manner.
For each checkpoint that required a revision you must indicate a revision for that checkpoint.
Intro. To DBMS 8
Project Assignment
Intro. To DBMS 9
Part II – The SQL Database
⚫
A binary version of your
database, suitable for opening with SQLite.
⚫ Data Files
⚫ SQL
◦Create
◦Queries
◦Insert/Delete
Intro. To DBMS 10
Grading
Project Element Points
Part I, Section I Database Descriptions 30 Part I, Section II User Manual 30 Part I, Section III Checkpoints 10
Part II SQL database 30
Intro. To DBMS 11
Roadmap
1. Collect requirements (users, views, constraints, tasks)
2. Model requirements and validate (ERD and EERD)
3. Map ERD to relational model
4. Verify model with relational algebra
5. Build model using DBMS (SQL)
6. Optimize model (Normalization and Indexing)
7. Build transactions and other user tools
Intro. To DBMS 12
DATABASE APPROACH
Intro. To DBMS 13
The Database Approach
⚫ Traditional file processing
◦ Each user defines and implements the files needed for a specific software application
⚫ Database approach
◦ Single repository maintains data that is
defined once and then accessed by various users
Database Management System (DBMS)
⚫ Defining
◦ Specifying what should be in the database
◦ Meta-data: description of what the data in the database means
⚫ Constructing
◦ Storing data in the database
◦ Handling file creation and organization on the disk
15
Database Management System (DBMS)
⚫ Manipulating
◦ Querying and updating the database
● Query: retrieving specific data from the database
● Update: change the contents of the database
⚫ Sharing
◦ Allowing users to access the database
16
Simplified Data Base System Environment
DBMS Languages
⚫ Data definition language (DDL)
• Defines internal and conceptual schemas
⚫ Storage definition language (SDL)
• Specifies the internal schema
⚫ View definition language (VDL)
• Specifies user views/mappings to conceptual schema
⚫ Data manipulation language (DML)
• Allows retrieval, insertion, deletion, modification
Characteristics of the Database Approach
A. Self-describing nature of a database system
B. Insulation between programs and data, and data abstraction
C. Support of multiple views of the data D. Sharing of data and multiuser
transaction processing
A. Self-Describing Nature of a Database System
⚫ Database system contains complete definition of structure and constraints
⚫ Meta-data
◦ Describes structure of the database
◦ “Data about data”
⚫ Database catalog used by:
◦ DBMS software
◦ Database users who need information about database structure
Terms
⚫ Database schema
◦ Description of a database
⚫ Schema diagram
◦ Displays selected aspects of schema
⚫ Schema construct
◦ Each object in the schema
⚫ Database state or snapshot
◦ Data in database at a particular moment in time
Database Schema and State
B. Insulation Between Programs and Data
⚫ Traditional file approach
◦ Data definition encoded in the application programs
⚫ Database approach
◦ Data abstraction
● Meta data allows program-data independence and program-operation independence
● Does not include details of how data is stored or how operations are implemented
Models -> Abstraction
⚫ Databases provide data abstraction
⚫ We use a data model to provide this abstraction
◦ Model provides the database structure
● Data types
● Relationships between data records
● Constraints on data records
24
Data Models
⚫ High-level model (conceptual model)
◦ How do users perceive the data?
● Entities
●real-world concepts or objects (Student)
● Attributes
●some property of an entity (GPA)
● Relationship – association between two entities
●“Students” are enrolled in “sections
⚫ Low-level model (physical data model)
◦ How is the data actually stored in the computer?
25
Data Models (continued)
⚫ Representational model (implementation model)
◦ Midway between high-level and low-level
● Still a way to organize the data so that it can be understood by users
● But also still related to how the data is physically stored
26
Data Models (continued)
●Relational data model
● A widely-used representational data model
● Will be the focus of this course
⚫ Other models
● Network model, hierarchical model
● Legacy models – not used as much these days
● Object data model
27
Database schema
⚫ Part of the data model
⚫ Description of database
◦ Use a schema diagram to display database schema
28
C. Support of Multiple Views of the Data
⚫ View
◦ Subset of the database
◦ Contains virtual data derived from the database files but is not explicitly stored
◦ Allows a level of access restriction
⚫ Multiuser DBMS
◦ Users have a variety of distinct applications
● A customer at Amazon.com needs to see his shopping cart
● A warehouse worker for Amazon.com needs to see a list of all the orders that need to be fulfilled from his warehouse
Three schema architecture
⚫ Promotes Program-data independence.
⚫ Multiple views of the data.
⚫ Each level provides mappings to levels above and below
⚫ Not always completely separate
Three-Schema Architecture
⚫ External or view level
◦ Describes part of the database that a particular user group is interested in
◦ User views
◦ Describes parts of database a user is interested in
◦ Hides the rest
◦ Different users, different views
Three-Schema Architecture
⚫ Conceptual level
◦ Describes structure of the whole database for a
community of users
◦ Hides details of physical storage
◦ Describes entities,
relationships, constraints, etc.
◦ Usually the level where the database is actually designed and implemented
Three-Schema Architecture
⚫ Internal level
◦ Describes physical storage structure of the database
D. Sharing of Data and Multiuser Transaction Processing
⚫ Transaction processing
◦ A set of accesses against a database is a transaction
⚫ Online transaction processing (OLTP) application
D. Sharing of Data and Multiuser Transaction Processing
⚫ Allow multiple users to access the database at the same time
◦ DBA, Designers. End Users, Analysts, programmers,
⚫ Concurrency control software
◦ Multiple users updating the database do so in a “sane” way
◦ No worries about locked files, or only one user accessing the system at a time
D. Sharing of Data and Multiuser Transaction Processing
⚫ Transactions are ACID
◦ Atomic – transactions are “all or nothing”
◦ Consistent – transactions must not put the database in a state that violates its constraints
◦ Isolated – transactions occur as if they happened by themselves
◦ Durable – completed transactions can survive system failure
Advantages of the database approach with DBMS
⚫ Controlling redundancy
◦ Design databases to store each logical item only once
⚫ Controlling access
⚫ Efficient query and update processing
◦ Everything in one place, rather than scattered around
37
Advantages of the database approach with DBMS
⚫ Backups!
⚫ Enforcing integrity constraints
◦ Data types
◦ Referential integrity
● Integrity across records in different tables
38
Advantages of the database approach with DBMS
⚫ Enforcing standards
⚫ Reduced application development time
◦ Most of your development work is already done once you’ve built the database
⚫ Flexibility
◦ Can change the database without changing every single application
⚫ Instant updates to information
39
When Not to Use a DBMS
⚫ Overhead!
◦ High initial monetary investment in hardware, software
◦ Security, referential integrity, concurrency control and other features don’t come for free
● Higher hardware costs than flat files
40
When Not to Use a DBMS
⚫ single-user database?
⚫ Limited data manipulation, cross referencing?
⚫ Speed is important
⚫ Requirements not expected to change at all
⚫ Limited storage capacity
41
Any other examples?
When would you use a spreadsheet instead?
So, you’ve decided to use the database approach…
You need to elicit requirements from users
Intro. To DBMS 42
Roadmap
1. Collect requirements (users, views, constraints, tasks)
2. Model requirements and validate (ERD and EERD)
3. Map ERD to relational model
4. Verify model with relational algebra
5. Build model using DBMS (SQL)
6. Optimize model (Normalization and Indexing)
7. Build transactions and other user tools
Intro. To DBMS 43
Database Users
⚫ End users
◦ Typical (or “naïve”) users
● Most users of a database
● Use canned screens and reports to do their job
◦ Sophisticated (or “power”) users
● Need better understanding of the database
● Write their own reports – often for one-off jobs
44
1/15/2016 45
More Users
⚫ Day-to-day users
⚫ Query users
accessing current information
⚫ Management users
⚫ Executive users
Also consider horizontal and vertical lines of organization
Even More Users
⚫ Systems Analysts & Software Engineers
◦ Determine what software the users need
◦ Write it for them
⚫ Database Administrators (DBA)
◦ Administer access rights, monitor the database for performance, etc.
⚫ Database Designers
◦ Design the layout of the database
● Often overlap with DBAs or Systems Analysts depending on the organization
46
Fact Finding
⚫ Many skills are involved in system development
⚫ Fact-finding and investigation of system requirements are two key skills
◦ Learn details of business processes and daily operations
◦ Become an expert in the problem domain
Intro. To DBMS 47
1/15/2016 48
Correctness
⚫ Validation
◦“Are we building the right model?”
⚫ Verification
◦“Are we building the model right?”
1/15/2016 49
Fact Finding
Fact Finding Methods
⚫ Distribute and collect stakeholder questionnaires
⚫ Review existing reports, forms, and procedure descriptions
⚫ Conduct interviews and discussion with users
⚫ Observe business processes and workflows
⚫ Conduct JAD sessions
1/15/2016 50
Questionnaires
Questionnaires
⚫ Limited and specific information from a large number of stakeholders
⚫ Preliminary insight
⚫ Not well suited for gathering detailed information
⚫ Open-ended questions vs. close-ended questions
1/15/2016 51
Existing Documentation
Review existing documentation
⚫ Forms, reports, user manuals, job descriptions, etc.
⚫ Purposes
◦ Preliminary understanding of processes
◦ Guidelines / visual cues to guide interviews
⚫ Identify business rules, discrepancies, and redundancies
⚫ Be cautious of outdated material
1/15/2016 52
Interviews
Interviews and Discussions
⚫ Most effective way to understand business functions and rules
⚫ Time-consuming and resource-expensive
⚫ May require multiple sessions
1/15/2016 53
Observation
Observe Business Processes and Workflow
⚫ From office walkthrough to performing actual tasks
⚫ May make users nervous
⚫ Not necessary to observe all processes at same level of detail
An Example
⚫ UNIVERSITY database (like Carmen)
◦ Information concerning students, courses, and grades in a university environment
◦ Data to represent each student, course,
section, and grade as a record in appropriate file
◦ Maintain relationships among the records
◦ Queries and Updates
An Example – Steps in the design
⚫ Phases for designing a database:
◦ Requirements specification and analysis
◦ Conceptual design
◦ Logical design
◦ Physical design
Sample Requirement:
Users
⚫ Who will be using the database?
◦ Internal
● Horizontal - across departments
● Vertical - clerical staff, middle management, and senior executives
● Day-to-day operations vs Administrative support
◦ External
● Customers
● Regulators
● Suppliers
56
Sample Requirement:
Data
⚫ What information does it need to hold?
◦ Student information
● Names, Ids, Majors, Year
● What courses they’ve taken, what grades they’ve gotten
◦ Course information
● Names, Course Numbers
● Any prerequisites required?
● Individual sections (Instructor, when it’s offered, room #, etc.)
57
An Example Requirement - Querys
⚫ Examples of queries:
◦ Retrieve the grade record
◦ List the names of students who took the 12:30 section of the ‘Intro to Database’
course offered in Winter 2011, and their grades in that section
◦ List all of the courses for a particular student
An Example Requirement - Maintenance
⚫ Examples of updates:
◦ Change the rank of ‘Smith’ to sophomore
◦ Add a new gradeable element for the
‘Database’ course for this semester
◦ Enter a grade of ‘A’ for ‘Smith’ in the
‘Database’ section of last semester
Sample Requirement:
Constraints
⚫ What kinds of checks do we need to put into place?
◦ Key constraint
● Does each student have a unique student ID?
◦ Referential constraint
● Does the section refer to a course that actually exists?
◦ Semantic constraint
● Implied, from the meaning of the situation
● Does the student have the pre-requisites for the class he is trying to enroll in?
60
Worksheet
⚫ DB Concepts
61
