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.
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.
When inserting the advisors records, separate first and lastname as they are separate fields in the table schema.
1. Insert the following classes’ records:
|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:
3. Insert the following students’ records:
|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.
Provide select statements to satisfy the following data requests:
- List all active male students assigned to Advisors 1 or 3 (Fred Stone or Jack Simpson).
- Provide a list of all students without a biography.
- What classes are in the English department?
- 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.
- How many students were born in the 1980s?
- Write a query to show the average GPA by gender.
- 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.
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.