Transcript Phase 1 - Personal.psu.edu

Kevin Banks
Reginald Monroe
Jessica Polivka
Christina Brinton
Christopher Runyon
Ira Burg
-
Project Lead
Project Planner
Data Analyst
Database Designer
Developer
Graphic Designer
 Offers
courses to the public
dealing with various natureoriented topics
 To register:
• mail in a registration form
• register by phone
• register in person
 The
process is time consuming
for both the student as well as
instructors of the course.
Database Management system that will meet the needs
of both instructors and students.
 Improves the registration efficiency and integrity for
students.
 Reduces the time spent by Instructors managing
registrations.
 Available 24 hours a day 7 days a week.
 Database is to be implemented to pass the 3 Normal
Forms of design to ensure that the database will meet
it’s stated requirements
 Work with the Bureaus other technology systems
 Designed with attention paid to the Entity Relationship
Diagram provided by the Bureau

 Organization
and Data Implementation- Based
upon the schema provided by the Bureau we are
to examine the tables and determine if they are in
3rd Normal Form
 Referential Integrity and Constraints
 The Bureau has requested that the database
meets the needs of seven basic functions that will
be undertaken by students and faculty
 Issues relating to security and backup of files
 security and access privileges
 Legal Issues
 Approximately Ten
pre-determined phases Team
Tuples utilizes to plan and implement databases
requested by our clients:
 Phase
1: Evaluation of the Database Server
Hardware
 Phase 2: Installing the Oracle Software
 Phase 3: Planning the Database
 Phase 4: Creating and Opening the Database
 Phase 5: Backing up the Database
 Phase 6: Enrolling System Users
 Phase 7: Implementing the Database Design
 Phase 8: Backing up the Fully Functional
Database
 Phase 9: Tuning the Database Performance
 Project
Scope
 Project
Management Plan
 Enroll
Systems Users
 Organize
and Implement data
 Implementation
of Referential Integrity
and Constraints
 Tune
the Database Performance
 Database Administrator: (Retain for 90 days)
• installing and upgrading the Oracle server and application tools
• allocating system storage and planning future storage requirements for
the database system
• creating primary database storage structures (table spaces) after
application developers have designed an application
• creating primary objects (tables, views, and indexes) once application
developers have designed an application
• modifying the database structure, as necessary, from information given
by application developers
• enrolling users and maintaining system security
• ensuring compliance with your Oracle license agreement
• controlling and monitoring user access to the database
• monitoring and optimizing the performance of the database
• planning for backup and recovery of database information
• backing up and restoring the database
 Application
Developer: (Retain for 90 days)
• develops the database application
• estimates storage requirements for an application
• specifies modifications of the database structure for an
application
• relays the above information to a database administrator
• tunes the application during development
• establishes an application's security measures during
development
 For
the Bureau of Parks and Recreation
the database needs to be normalized in
the third normal form (3NF). In order to
meet this requirement the database
needs to confirm to the first normal form
(1NF) and the second normal form (2NF).
 To
meet the first normal form the data must
meet the following requirements:
• The data needs to contain no duplicate rows. Any
duplicate data needs to be removed from the
database.
• Every row and column contains exactly one value
from the applicable domain. Any erroneous data
must be removed from the database.
• All columns must not contain any hidden
information such as timestamps or object ID’s. In
addition there needs to be a primary key
associated with the data fields.
 To
meet the requirements for second
normal form the database will need to
adhere to the following requirements:
• The table must be 1NF.
• All subsets of data that are contained in multiple
rows need to be separated into their own tables.
• The data will then need to establish relationships
with the new tables using foreign keys.
 1NF
and 2NF on the database will help
reduce data redundancy and prevent data
anomalies on future data
 For
the third normal form the following
requirements must be met:
• The table must be in 2NF
• All database attributes must rely on the primary
key.
 Based
on this analysis the database is not 3NF
compliant.
• missing foreign keys the database does not meet the 2NF
requirement
• database design does not indicate primary keys exist within
the database which disqualify the database for 3NF compliance
 To
meet 3NF standards is to include a primary
key that can be shared with all data attributes
and establish relationships between tables
using foreign keys
Note: In depth Table design & SQL Query supplied in the Proposal

The Parks and Recreation Department would like to
enforce the following business rules within their existing
system along with the recommendations on how to enforce
these rules.
• A COURSE may consist of one or more CLASSes.
• An INSTUCTOR may teach one or more CLASSes.
• A STUDENT may enroll in many CLASSes.
• A STUDENT can take more than one CLASS, and each CLASS contains many
•
•
•
•
•
•
STUDENTS.
A CLASS is taught by only one INSTRUCTOR, but an INSTRUCTOR can teach many
CLASSes.
A COURSE consists of many CLASSes, while each CLASS is based on one COURSE,
so there is a one-to-many relationship between COURSE and CLASS.
A STUDENT may not enroll him/herself for two CLASSes of the same COURSE.
An INSTRUCTOR may not teach two CLASSes of the same COURSE.
The date in the CLASS table must be equal or greater than August, 2002.
The time in the CLASS table must be between 8:00 AM and 3:00 PM.
Note: In depth Table design & SQL Query supplied in the Proposal

SQL statements will be executed after the user (student or instructor)
is logged into the system. The data retrieved as well as the actions that
the user can do (such as add information to a table) via the SQL
statements will be secured based on the user’s role/id.
• students to enroll in a class
• instructors to add course and class information
• student to search the database for course titles, course sections, class
•
•
•
•
times, dates, and instructors
list all courses that have more than 10 students enrolled
student to query the database to see what classes s/he is registered for;
List course ID, class section, date and time
instructor to query the database for what student names are registered for
his/her class
instructor to query the database for information for course name, class
section, instructors, dates, and number of students

The following keys will be established through the business rules
requested by the Bureau:
• COURSE table – course_id (PK)
• CLASS table – course_id & course_section (PK)
• INSTRUCTOR table – instructor_id (PK)
• INSTRUCTOR_CLASS table – instructor_id, course_id, & class_section (PK/FK)
• STUDENT table – student_id (PK)
• STUDENT_CLASS table – student_id, course_id, & class_section (PK/FK)

Since the tables are already established, the following SQL statements
can be used to modify the tables to implement these keys:
• (replace [table] in statements with table name and replace [field] in statements
with field name(s))
• ALTER TABLE [table]
add CONSTRAINT [field]_pk PRIMARY KEY ([field])

For tables with more than one field making up the key:
• ALTER TABLE [table]
add CONSTRAINT [field]_pk PRIMARY KEY ([field], [field], [field])





Business Rule:
A CLASS is taught by only one INSTRUCTOR, but an INSTRUCTOR
can teach many CLASSes:
Relationship between the INSTRUCTOR and CLASS tables to the
INSTRUCTOR_CLASS is one-to-many
Tables can be linked via the keys that were established
Define class_section field in the INSTRUCTOR_CLASS table to not
allow NULL values
• Every CLASS in the CLASS table must have a class_section
• In the CLASS table, class_section is defined to not allow NULL
values
• The class_section value will be passed to the
INSTRUCTOR_CLASS table from the CLASS table when an
instructor is assigned to teach a class

Business Rule:

An INSTRUCTOR may not teach two CLASSes of the same COURSE.




INSERT statement will be used to insert the COURSE/CLASS
recoding into the INSTRUCTOR_CLASS table
Enforce this rule by using an EXISTS condition in the INSERT
statement
• Check the combination of instructor_id and (course_id and
class_section) in the INSTRUCTOR_CLASS table
If EXISTS condition is TRUE, do not allow insert
Recommend to also check the time and date fields in the CLASS
table with an EXISTS condition to make sure that the INSTRUCTOR is
available




Business Rules:
The date in the CLASS table must be equal or greater than
August, 2002.
The time in the CLASS table must be between 8:00 AM and 3:00
PM.
Add CHECK CONTRAINTS to the CLASS table
• Check the class_date field
 Must be greater than or equal to 8/1/2002
• Check the class_time field
 Must be between 8:00 AM and 3:00 PM



SQL statements/queries will be executed after the
user (student or instructor) is logged into the system
Users’ security to database records will be linked to
their user id
The sign-on id for any user will be their email address
which is stored in the STUDENT or INSTRUCTOR table
respectively
• USERID = STUDENT.Student_Email OR
• USERID = INSTRUCTOR.Instructor_Email



Requested Query:
List all courses that have more than 10 students
enrolled.
SELECT course_name, s.class_section,
COUNT(student_id) as "Student_Count“
FROM student_class s, course c, class a
WHERE s.course_id = a.course_id AND
s.class_section = a.class_section AND a.course_id =
c.course_id
GROUP BY course_name, s.class_section
HAVING COUNT(student_id) > 10


Requested Query:
For the instructor to query the database for what student
names are registered for his/her class.
• SQL statement is based on the instructor entering the course_id and
class_section in which they are teaching which will act as variables and
will be passed to the statement
• For this example the instructor is Terry Smith and is searching for the
Endangered Species class (course_id = 4 AND class_section = 1)
• If needed, other SQL statements could be created to list all courses and all
students enrolled in those courses that the logged in instructor is teaching

SELECT b.course_id, b.class_section, course_name, student_name
FROM student s, student_class a, course c, class b
WHERE s.student_id = a.student_id AND a.course_id = b.course_id
AND a.class_section = b.class_section AND b.course_id = c.course_id
AND a.course_id = 4 AND a.class_section = 1





Database Backup and Recovery:
Run a hot (online) backup on a daily basis - insure any new data
coming in on a daily basis will have minimal loses if there is a
problem
Cold (offline) backup is performed once a month
either tape or online backup
• Our recommendation is to use the tape backup - most secure
approach
 strongly recommended that they be stored in an off site
vault
• While every effort is made to secure online backup, there is
still risk.
Very important for your company to have a well documented
Backup and Recovery plan in place



Data Security:
“80 percent of data security breaches involve insiders, employees or
those with internal access to an organization, putting information at
risk.”
Proper security measures to each individual who will use the system
mandatory to fill out a user profile
• password protected accounts
• Ids will be assigned according to privileges
•

Instructors have the ability to manage the system as a whole
adding and deleting classes
• updating information system wide
• adding and deleting students or instructors from courses
•

Students
viewing course information
• Submitting & canceling registration information
• restrict from signing up for a full class
•
 Disclaimer:
• Link at the top of the web page, where users will have
easy access to it
• Will relieve you of any responsibility should a problem
with the content of your web site arise.
 Privacy
Statement:
• This should be located as a link, next to the Disclaimer at
the top of the homepage.
• Lets the user know what information has been collected,
how this information will be used and protected
 Terms
and Conditions for Use
• outlines the refund policy, attendance policy and
ramification of violating any of the terms and conditions
 Team Tuples
is proud to present our proposal to
the Bureau of Parks and Recreation for the design
and implementation of a Database Management
system that will meet the needs of both instructors
and students. Upon implementation of the
recommendations, the Bureau of Park and
Recreations will have a database to meet their
three most critical needs:
• Improves the registration efficiency and integrity for
students.
• Reduces the time spent by Instructors managing registrations
• Is available 24 hours a day 7 days a week.