SQL for data management

Oct 11th, 2014
SKTFaker
Category:
Programming
Price: $40 USD

Question description

The class is delivered with SQL Server Express and SQL Server Management Studio. Students are encouraged to use the same software. If the student experiences problems with the installation they may use a comparable database like Oracle XE or MySQL.

The instructor will provide installation instructions upon download of the database software or assist students as required.

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

StudentIDint
FirstNamestring
LastNamestring
BirthDatedate
Genderstring
StartDatedate
GPAnumber
IsActiveboolean
Biostring
AdvisorIDint

Advisors

AdvisorIDnumber
FirstNamestring
LastNamestring
EmailAddrstring

Classes

ClassIDint
ClassCodestring
ClassNamestring
Descriptionstring

Students_Classes

StudentClassIDint
StudentIDint
ClassIDint
StartDatedate
Assignment1number
Assignment2number
Assignment3number
Assignment4number
ClassGPAnumber

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.

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.

Your deliverable should be a Word document with the DDL script necessary to recreate all tables and relationships. Focus on proper datatypes, keys, identities, and constraints. Future assignments will depend on your design, so it is best to try and stick to the base specs. In addition to the DDL script, provide a screenshot of a diagram showing all tables and the relationships between them.

Your document will be used over the duration of this course for each of the weekly deliverables. Create an appropriate title page and table of contents with entries for each of the 5 weekly IP assignments (Week 1 IP, Week 2 IP, etc…). Content for this assignment would then correspond to the Week 1 IP.

Tutor Answer

(Top Tutor) Daniel C.
(997)
School: University of Maryland
PREMIUM TUTOR

Studypool has helped 1,244,100 students

7 Reviews


Summary
Quality
Communication
On Time
Value
kpcutie
Dec 5th, 2016
" Excellent job "
Hemapathy
Nov 21st, 2016
" all I can say is wow very fast work, great work thanks "
BlueOcean
Nov 8th, 2016
" Awesome! Exactly what I wanted. "
kevin12622
Oct 30th, 2016
" Goes above and beyond expectations ! "
ashleyisgod
Oct 16th, 2016
" Top quality work from this guy! I'll be back! "
likeplum4
Oct 7th, 2016
" Excellent work as usual "
Molly_Moon
Sep 24th, 2016
" AMAZING as always! "
Ask your homework questions. Receive quality answers!

Type your question here (or upload an image)

1827 tutors are online

Brown University





1271 Tutors

California Institute of Technology




2131 Tutors

Carnegie Mellon University




982 Tutors

Columbia University





1256 Tutors

Dartmouth University





2113 Tutors

Emory University





2279 Tutors

Harvard University





599 Tutors

Massachusetts Institute of Technology



2319 Tutors

New York University





1645 Tutors

Notre Dam University





1911 Tutors

Oklahoma University





2122 Tutors

Pennsylvania State University





932 Tutors

Princeton University





1211 Tutors

Stanford University





983 Tutors

University of California





1282 Tutors

Oxford University





123 Tutors

Yale University





2325 Tutors