sql program 1

Stuck with a difficult assignment? No time to get your paper done? Feeling confused? If you’re looking for reliable and timely help for assignments, you’ve come to the right place. We promise 100% original, plagiarism-free papers custom-written for you. Yes, we write every assignment from scratch and it’s solely custom-made for you.


Order a Similar Paper Order a Different Paper

You have to use amazon workspace. I will give you access to it once someone is assigned.


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 (click here for a video instruction to create a database with tables):

  • 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.
Writerbay.net

We’ve proficient writers who can handle both short and long papers, be they academic or non-academic papers, on topics ranging from soup to nuts (both literally and as the saying goes, if you know what we mean). We know how much you care about your grades and academic success. That's why we ensure the highest quality for your assignment. We're ready to help you even in the most critical situation. We're the perfect solution for all your writing needs.

Get a 15% discount on your order using the following coupon code SAVE15


Order a Similar Paper Order a Different Paper