Degree Programs

For each part Your deliverable should be a Word document with screenshots showing the sample database and table you have created, and discuss the issues that you had for this project related to SQL Server and how you solved them.

. It should contain all of these elements:  a Title page,  an introductory paragraph that describes the purpose in objective of the assignment,  brief narrative writing in the body of the paper that describes your processes for creating the database and the table (how did you create these? Code? Management Studio Interface? See “.  neatly formatted and labeled screenshots that are described in the paper, and references (in-text citations in the body of the paper and the full reference in the reference list.

Part 1

You are tasked with creating the database to store information for a university of your choosing. After initial design meetings with your client, the following business rules have been determined :

• A student has a name, a birth date, and gender.
• You must track the date the student started at the university and his or her current GPA, as well as be able to inactivate him or her without deleting information.
• For advising purposes, store the student’s background/bio information. This is like a little story.
• An advisor has a name and an e-mail address.
• Students are assigned to one advisor, but one advisor may service multiple students.
• A class has a class code, name, and description.
• You need to indicate the specific classes a student is taking/has taken at the university. Track the date the student started a specific class and the grade earned in that class.
• Each class that a student takes has 4 assignments. Each assignment is worth 100 points.

The following entity tables have been composed:

Students
StudentID	int
FirstName	string
LastName	string
BirthDate	date
Gender	string
StartDate	date
GPA	number
IsActive	boolean
Bio	string
AdvisorID	int
Advisors
AdvisorID	int
FirstName	string
LastName	string
EmailAddr	string
Classes
ClassID	int
ClassCode	string
ClassName	string
Description	string
Students_Classes
StudentClassID	int
StudentID	int
ClassID	int
StartDate	date
Assignment1	number
Assignment2	number
Assignment3	number
Assignment4	number
ClassGPA	number

Create a database in an SQL server, and translate this design into SQL server tables with appropriate fields. The data types above are general types. An int is an integer whole number. A number is a real number with a decimal point and fractional digits to the right of the decimal point. A string could be a single character, a few characters or even multiple paragraphs of text. Each DBMS may have several specific data types for each general type. The specific data type that you select will depend on the size and range of the values that will be stored in the field. The datatype bit can be used for Boolean fields.

In the Students_Classes table, the assignment fields are used to record a grade for each of the 4 assignments in the class. Values range from 0–100. The GPA fields will store values from 0.00–4.00. In the Students table, the gender field will record an M or F.

All primary keys should be specified as “identity” or “auto-increment” fields, which means that the database management system will create a unique value for the field, each time a new row is added to the table. By default, the value starts at 1 for the first row added and increments by 1 for each new row inserted.

Part2.

When inserting the advisors records, separate first and lastname as they are separate fields in the table schema.

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	Bio	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.

Part 3

Provide select statements to satisfy the following data requests:

1. List all active male students assigned to Advisors 1 or 3 (Fred Stone or Jack Simpson).
2. Provide a list of all students without a biography.
3. What classes are in the English department?
4. Create a list of all students and their advisors. Sort by the advisor’s name and then the student’s name. Include the student’s birth date, gender, and GPA.
5. How many students were born in the 1980s?
6. Write a query to show the average GPA by gender.
7. Provide a list of all advisors and the number of active students assigned to each. Filter out any advisors with more than 1 student.

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.

Part 4

Using the database completed so far, extend the design to accommodate degree programs. The new design should incorporate the following functionalities:

• A degree has a name and description.
• What degree is the student working towards? A student can only work on one degree at a time.
• What classes are necessary to obtain a specific degree?
• 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.
• 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.