Search results for 'Patton-Fuller Database Design Process Project DBM/502'

GU CS371 Database Design Week 7 Assignment Chapter 8 Final Project

Fundamentals of Database Management Systems 2nd Edition
Questions and Task:
Question 1
In your own words discuss the benefits of normalization.

Question 2
Do you think we should normalize our designs to higher levels than 3NF? Why or why not?

Question 3
There are 14 physical database design techniques discussed in chapter 8. The goal of a quality physical design is to improve performance while disrupting the logical design as little as possible. Pick a technique, which you believe to be the most beneficial in accomplishing the above goal, and explain your reasoning.

Question 4
Discuss the concept of an index and explain how they improve performance. Assignment "Final Project Overview" You just took a job with a University and have been asked to track some information about the courses they offer. The previous employee was using a spreadsheet to track this information. The University is expanding from three classes to three hundred classes. Enrollment is expected to increase from around 20 students to 5,000. Your supervisors are not very tech savvy and they simply ask you to continue maintaining the spreadsheet. Review the spreadsheet labeled final_project.xlsx.

Explain the issues that exist with maintaining the data in its current form.
Explain the process of migrating the data in its current form to a well formed data model by highlighting the following in detail:
Identify required attributes that need to be tracked
Identify functional dependencies Show the redesign in 1NF, 2NF, and 3NF (similar to figures 7.29, 7.31, 7.33)
Explain the benefits of the data in its new form.
Create the E-R diagram of your relational tables using MySQL Workbench.
Provide the SQL statement required to create at least one of your tables.
Provide the SQL statement required to delete at least one of your tables.
Provide the SQL statement required to create a view which includes Course #, Course Name, Time, and Days only.
Identify at least one index, which you believe would improve performance.
Explain your choice.
Propose some additional data items, which you believe may be beneficial to the University if tracked in your database (i.e. Descriptions, more info about the instructors and students, departments, etc).
Describe the steps required to implement these additions.
If you add a course description attribute to your course table, there may be some performance impacts imposed by the addition of this text field.
Discuss a physical design technique to improve the performance of the new course description field.

ITCO333 Data Modeling and Design Unit 2 Individual Project
Deliverable Length: 1 Word document and 1 .accdb file

Details: Create a SQL Server database called: ITCO333Database. Using Data Definition Language (DDL) and Data Manipulation Language (DML) you will create the below DEPARTMENTS and EMPLOYEES tables. Before you begin creating the new tables, review your tables created in Unit 1 IP to ensure that your database is in third normal form (3NF).

Part 1:
Your ITCO333 database should contain data related to the organizational departments in your Unit 1 chosen topic. Therefore, create a DEPARTMENTS table with the following field specifications:
Field Name Data Type Other Comments and Requirements
Department_ID   int Primary Key
Department_Name nvarcha(50)
Insert at least four records of sample data into the DEPARTMENTS table.

Your ITCO333 database should contain data related to employees in your Unit 1 chosen topic. Therefore, create an EMPLOYEES table with the following field specifications:
Field Name Data Type Other Comments and Requirements
Employee_ID Int Primary Key Last_Name nvarchar(50) Cannot be null.
First_Name nvarchar(50) Cannot be null.
Birth_Date datetime
Employment_Start_Date datetime
Hourly_Pay decia(p[,s]) Must be greater that 0
Department_ID Int Related to the DEPARTMENTS table.
Create a foreign key constraint.
Manager_ID Int Related to the Employee_ID in this table.
Create a foreign key constraint.
Insert at least eight records of sample data into the EMPLOYEES table.

Part 2: Using your Unit 1 ERD, create tables, fields, primary keys and relationship constraints in your ITCO333Database.
Insert your Unit 1 sample data into the newly created tables.

Part 3: Generate a SQL Server Database Diagram.

Additional Requirements: All tables must be in Third Normal Form (3NF). This may require you to normalize your Unit 1 data. Be sure to incorporate Instructor feedback from your Unit 1 IP. Use the following data types:
Integers: int
Decimals: decimal(p[,s])
Strings: nvarchar(50)
Date/Time: datetime

Deliverable: One Word document with:
SQL DDL to create database
SQL DDL to create tables (including EMPLOYEES and DEPARTMENTS tables)
SQL DML to insert data (including EMPLOYEES and DEPARTMENTS sample data)
SQL Server Database Diagram

The aforementioned SQL DML must be in text format (no screen shots).

“Martial Arts R Us” (MARU) needs a database. MARU is a martial arts school with hundreds of students. It is necessary to keep track of all the different classes that are being offered, who is assigned to teach each class, and which students attend each class. Also, it is important to track the progress of each student as they advance.
Create a complete Crow’s Foot ERD for these requirements:
• Students are given a student number when they join the school. This is stored along with their name, date of birth, and the date they joined the school.
• All instructors are also students, but clearly, not all students are instructors. In addition to the normal student information, for each instructor, the date that they start working as an instructor must be recorded, along with their instructor status (compensated or volunteer).
• An instructor may be assigned to teach any number of classes, but each class has one and only one assigned instructor. Some instructors, especially volunteer instructors, may not be assigned to any class.
• A class is offered for a specific level at a specific time, day of the week, and location. For example, one class taught on Mondays at 5:00 pm in Room #1 is an intermediate-level class. Another class taught on Mondays at 6:00 pm in Room #1 is a beginner-level class. A third class taught on Tuesdays at 5:00 pm in Room #2 is an advanced-level class.
• Students may attend any class of the appropriate level during each week so there is no expectation that any particular student will attend any particular class session. Therefore, the actual attendance of students at each individual class meeting must be tracked.
• A student will attend many different class meetings; and each class meeting is normally attended by many students. Some class meetings may have no students show up for that meeting. New students may not have attended any class meetings yet.
• At any given meeting of a class, instructors other than the assigned instructor may show up to help. Therefore, a given class meeting may have several instructors (a head instructor and many assistant instructors), but it will always have at least the one instructor that is assigned to that class. For each class meeting, the date that the class was taught and the instructors’ roles (head instructor or assistant instructor) need to be recorded. For example, Mr. Jones is assigned to teach the Monday, 5:00 pm, intermediate class in Room #1. During one particular meeting of that class, Mr. Jones was present as the head instructor and Ms. Chen came to help as an assistant instructor.
• Each student holds a rank in the martial arts. The rank name, belt color, and rank requirements are stored. Each rank will have numerous rank requirements. Each requirement is considered a requirement just for the rank at which the requirement is introduced. Every requirement is associated with a particular rank. All ranks except white belt have at least one requirement.
• A given rank may be held by many students. While it is customary to think of a student as having a single rank, it is necessary to track each student’s progress through the ranks. Therefore, every rank that a student attains is kept in the system. New students joining the school are automatically given a white belt rank. The date that a student is awarded each rank should be kept in the system. All ranks have at least one student that has achieved that rank at some time.

Murach’s SQL for SQL Server Chapter 10 How to create and maintain databases and tables with SQL statements
Exercises
1. Create a new database named Membership.
2. Write the CREATE TABLE statements needed to implement the following design in the Membership database. Include reference constraints. Define IndividualID and GroupID with the IDENTITY keyword. Decide which columns should allow null values, if any, and explain your decisions. Define the Dues column with a default of zero and a check constraint to allow only positive values.
3. Write the CREATE INDEX statements to create a clustered index on the GroupID column and a nonclustered index on the IndividualID column of the GroupMembership table.
4. Write an ALTER TABLE statement that adds a new column, DuesPaid, to the Individuals table. Use the bit data type, disallow null values, and assign a default Boolean value of False.
5. Write an ALTER TABLE statement that adds two new check constraints to the Invoices table of the AP database. The first should allow (1) PaymentDate to be null only if PaymentTotal is zero and (2) PaymentDate to be not null only if PaymentTotal is greater than zero. The second constraint should prevent the sum of PaymentTotal and CreditTotal from being greater than InvoiceTotal.
6. Delete the GroupMembership table from the Membership database. Then write a CREATE TABLE statement that recreates the table, this time with a unique constraint that prevents an individual from being a member in the same group twice.

CS362 Structured Query Language for Data Management Week 2 IP

After creating the database schema, use Insert, Update, and Delete commands to populate the tables with the following information.

1. Insert the following classes' records:
Code     Name     Description
ACCT306     Accounting 1     This course introduces accounting concepts and explores the accounting environment. It covers the basic structure of accounting, how to maintain accounts, use account balances to prepare financial statements, and complete the accounting cycle. It also introduces the concept of internal control and how to account for assets.
CS362     Structured Query Language for Data Management     This course gives complete coverage of SQL, with an emphasis on storage, retrieval, and manipulation of data.
ENG115     English Composition     In this course, students focus on developing writing skills through practice and revision. Students will examine expository, critical, and persuasive essay techniques.
FIN322     Investments     This course focuses on investments and investment strategies. Various investment vehicles such as stocks, bonds, and commodities are examined. Students will explore the principles of security analysis and valuation.

2. Insert the following advisors' records:
Name     Email
Fred Stone     fred@college.edu
Bob Gordon     bob@college.edu
Jack Simpson     jack@college.edu

3. Insert the following students' records:
Name     Birthdate     Gender     StartDate     GPA     IsActive     AdvisorID
Craig Franklin     1970-03-15     Male     2010-05-30     3.10     Yes     3
Harriet Smith     1982-04-15     Female     2010-05-30     3.22     Yes     1
George David     1984-11-05     Male     2010-10-01     0.00     Yes     3
Ben Jefferson     1976-09-25     Male     2009-02-21     1.80     No, the student has gone on temporary leave to pursue other opportunities but plans on returning in 1 year.     3

4. Delete the course named Investments from the system.

5. Change Harriet Smith’s birthdate to April 25, 1982 and her GPA to 3.25.

Copy and paste the work into your Key Assignment document and include screen shots of each step, describe what you did for each step and paste in the actual SQL text used to perform each step. Provide an introduction explaining the important of these commands in relation to your overall Key Assignment.

CS362 Structured Query Language for Data Management Week 4 IP Extend the Design

Using your Week 1 IP as a starting point, extend the design to accommodate degree programs. The new design should incorporate the following functionalities:

1. A degree has a name and description.
2. What degree is the student working towards? A student can only work on one degree at a time.
3. What classes are necessary to obtain a specific degree?
4. Provide the DDL script to add the tables for the new design changes. Include an updated diagram that shows all the tables in the system.
5. Write the DML script to insert 3 test records in each of the tables in the system. This data will be necessary to write the queries in the next assignment.

Copy and paste the work into your Key Assignment document and include screen shots of each step, describe what you did for each step and paste in the actual SQL text used to perform each step. Upload your document to the Submitted Tasks section.

CS362 Structured Query Language for Data Management Week 5 IP Functions Stored Procedure and Indexes

This assignment requires you to take your extended design from Week 4 IP and add proper indexes, a function, and a stored procedure. Your changes will provide functionality used by the teachers for screens such as a grade book. Be sure any code is properly formatted and has appropriate comments.

Part 1: Function
Write a user-defined function (UDF) that calculates a student's GPA for a given time frame. Inputs are StudentId int, ClassStartDateStart datetime, and ClassStartDateEnd datetime. The output should be the student's GPA for all classes that were taken between ClassStartDateStart and ClassStartDateEnd. Also, supply the script to call this new function, passing it parameter values of your choice.

Part 2: Stored Procedure
Write the DDL script to make a stored procedure that returns data needed to display a grade book screen for a professor. The only input for the stored procedure is a ClassId. Outputs need to include student names and grades for all assignments as well as a calculated overall grade for the class for each student. Provide an example calling this new stored procedure, passing it parameter values of your choice. Include a screenshot of the output.

Part 3: Indexes
Provide a list of suggested indexes and the DDL script to create them. Include an explanation of the purpose of indexes and how you made your decision for the fields to include in your list of suggested indexes.

Copy and paste the work into your Key Assignment document and include screen shots of each step, describe what you did for each step and paste in the actual SQL text used to perform each step. Upload your document to the Submitted Tasks.

CS251 Fundamentals of Database Systems Week 4 IP The Microsoft Access Database

Using Microsoft Access create the tables and relationships defined in your data model. Your Microsoft Access Database at this point should include the following:

The Tables as defined in your Entity Relationship Diagram.
The Relationships between the tables.
3 Defined Queries. One of the queried needs to be created to show the data from the Many-to-Many relationship (or include three tables) originally identified from the initial requirements.
Take a screenshot of the ERD and the Queries.

Add the Screenshots to the section in your template titled: The Microsoft Access Database.

Unit 4 Individual Project: Database SQL Server

Deliverable Length:
1 Database Diagram; DDL for 7 Tables

Details:
The following is the same database diagram from Unit 3:

Using this diagram, address the following:
Establish the relationships between the tables in the SQL server environment.
Ensure that all primary keys are properly created and that the foreign key columns are defined correctly.
Make sure that the 3 additional tables you added in Unit 3 are also shown and have established primary and foreign keys to appropriate tables.
Update the database diagram, and generate the data definition language (DDL) for the new tables.
 Describe what changed in the DDL for each table.
 Describe any changes that were needed from the original model to create the relationships.
Submit a consolidated Word document with all screenshots and the DDL.