CSIS 325 – Lab 4 (SQL CREATE TABLE)

Before beginning this lab, be sure you have successfully downloaded and installed SQL Server Express 2014: (You should have already done this in your first lab, but if you ran into any problems, here is the link again.)

https://www.microsoft.com/en-us/download/details.aspx?id=42299

Struggling to find relevant content? Order a custom essay on
CSIS 325 – Lab 4 (SQL CREATE TABLE)
Let our experts save you the hassle
Order Now

Instructions:

In SQL Server, create a new database called University.

Given the ERD in this document, write the CREATE TABLE statements to construct the tables for this database.  Be sure to use reasonable data types and create all tables, attributes, primary keys, foreign keys, and “not null” constraints in your statements where appropriate.

To receive full credit for this assignment, your CREATE TABLE statements must be executed in SQL Server.

In the space provided below the ERD, type your CREATE TABLE statements.  Then, in SQL Server, after you execute each CREATE TABLE statement, issue these two statements:

SELECT * FROM [table name]

exec sp_help [table name].

Note:  exec sp_help [table name] is a built-in stored procedure that you run to easily see your table structure, including fieldnames, data types, primary keys, foreign key constraints, etc.  It’s a very useful tool.

Take a screen shot of your queries and their results and paste the screenshot below each CREATE TABLE statement.  Put each table on a separate page of this document.

To illustrate, the Campuses table has been created for you on the page following the ERD and Narrative Description section.

Related: [SOLVED] An ad agency tracks the complaints, by week received, about the billboards 

Before you begin…a few helpful hints:

  1. Making mistakes (we all do)

Don’t forget that you can use the ALTER TABLE command to go back and add constraints or change fields if needed.  Many times, you may find it easier to simply delete the table and start over.  To delete an entire table, enter DROP TABLE [table name], and begin again.  You can also use the DROP command to delete your entire database.  To do this, however, you must be “in” a different database.  That is, you cannot drop a database that you are currenting “using”.  To drop a database, “USE” a different database other than the one you’re deleting and then enter DROP DATABASE [database name].

 

  1. Multiple fields as the primary key

Some of the entities in the ERD below (namely the weak entities) will require you to specify multiple fields as the primary key (i.e. a composite primary key). To do this, simply separate the fields with a comma in your primary key constraint.  For example, if you have a weak entity InvoiceItems, whose primary key consists of an InvoiceID from an Invoices entity and an InvoiceItemID from the InvoiceItems entity, your primary key constraint would appear as follows:

CONSTRAINT pk_InvoiceItems PRIMARY KEY (InvoiceID, InvoiceItemID) ß Note that two fields are listed

You will also need to specify where the InvoiceID comes from in your foreign key constraint:

CONSTRAINT fk_InvoiceItems_Invoices FOREIGN KEY (InvoiceID) REFERENCES Invoices

 

  1. Many-to-many relationships

Don’t forget that many-to-many relationships result in a “bridge” table that are not shown on the ERD but must be constructed in your database.

 

  1. Minimum cardinalities and NOT NULL constraints

Pay close attention to your minimum cardinalities.  Depending on where they exist, you may have to include “not null” constraints on certain fields in your table.  For instance, notice that a Building MUST be assigned to a Campus in the ERD below.  Consequently, when constructing your Buildings table, you have to ensure that the CampusID field is not null.  You can do this with a NOT NULL constraint.

 

  1. Order matters

Be sure that when you execute your CREATE TABLE statements, you must actually execute the query to create the Campuses table in SQL Server, even though it has been done for you below.  Furthermore, it must be executed first.  Recall that order matters.  That is, you cannot create a table (such as Buildings) that contains a reference to other tables (i.e. Campuses and ZipCodes) until the other tables have been created.

 

  1. ON DELETE

Note that you do not have to include the ON DELETE, ON UPDATE, etc. commands in your CREATE TABLE queries for this lab.  In most cases, you will want the desired action to be “RESTRICT”.  In fact, this is the default behavior of SQL Server, and if you try to include the ON DELETE RESTRICT statement in your CREATE TABLE, you will get an error.  You should only include ON DELETE, UPDATE etc. if you want the behavior to be something other than RESTRICT, such as CASCADE.

 

  1. Primary Keys

It is a good practice to create your own primary key (as illustrated in the ERD).  The int (integer) data type is especially good for primary keys.  An exception to this rule is ZipCode.  Notice that ZipCode is the primary key of the ZipCodes table.  It represents an actual ZipCode (like 24515).  As such, a zipcode (as well as a social security number or phone number) should never be declared as an integer data type.  If you declare a field as an integer, leading zeros will be dropped.  If you have a ZipCode such as 02134, it will incorrectly be stored as 2134 because the leading zero will be dropped.  A good rule of thumb in determining data types is to determine if the field will ever be used in a calculation.  If it will be used in a calculation, declare it as a numeric data type.  Otherwise, use varchar or char.

 

  1. Data types

While we are on the topic of data types, be especially careful to declare fields that contain dates as “date” data types.  Under the hood, “date” fields are stored as numbers, which means you can perform calculations with them.  You can find the differences between dates and other numbers using the datediff() function.  You can add dates to dates or other numbers using the dateadd() function.  You can even retrieve the current date using the getdate() function.  Most SQL Server installations use “date” or “smalldatetime” for date fields.  Keep this in mind when you are declaring your date fields for the University database in this lab.

 

  1. Composite foreign keys

Oftentimes, you will inherit more than one field from a table as a foreign key.  An example is an invoice for a doctor’s visit where the invoice is associated with a particular diagnosis, and the diagnosis itself consists of a DiagnosisCategoryID and DiagnosisItemID as its primary key.  Note the ERD below, which shows DiagnosisItems as a weak entity, which is dependent on the existence of a DiagnosisCategory.  As a weak entity, DiagnosisItems inherits DiagnosisCategoryID as part of its primary key.  To include a DiagnosisItem in your Invoices table, you must include its entire primary key, which means DiagnosisCategoryID, as well.

CREATE TABLE Invoices(

DiagnosisCategoryID int,

DiagnosisItemID int

)

In this case, both DiagnosisCategoryID and DiagnosisItemID must be referenced in your foreign key constraint and point to the one DiagnosisItems table.  The constraint would appear like this:

CONSTRAINT fk_Invoices_DiagnosisItems FOREIGN KEY (DiagnosisCategoryID, DiagnosisItemID) REFERENCES DiagnosisItems

Many students are tempted to break this foreign key constraint into two separate constraints, but this is incorrect.  They erroneously create one constraint that references the DiagnosisCategories table and another that references the DiagnosisItems table.  As such, any given DiagnosisCategory may exist in the DiagnosisCategories table, and any given DiagnosisItem may exist in the DiagnosisItems table; however, it is the COMBINATION of these two items in the DiagnosisItems table that is important.  Therefore, always ensure that you have a SINGLE foreign key constraint if you are referencing multiple fields in a table that represent a single relationship.

Read also: [SOLVED] CSIS 325 – Lab 2 (ER Diagrams)

Now, finally, here is your assignment…

 

 

 

ERD for the University database

 

 

Narrative Description of the University Database

Students take courses at the university.  A student can serve as a mentor for one or more other students, but he/she is not required to do so.  Further, each student can have one other student as his/her mentor, but this, too, is not required.  A student, however, is required to be assigned to one and only one faculty member.  In turn, a faculty member advises many students but is not required to advise any students.

Courses are offered in multiple sections.  A course consists of a prefix as well as a course number.  For example, in this course, CSIS is the course prefix, and 325 is the course number.  CSIS 325 is offered in multiple sections, some residentially and some online.  All residential sections must be taught in a classroom on a campus; whereas online classes are not assigned to a classroom.  Sections are represented by a three-digit section number such as 001, 002, B01, etc.  A given section is identified by a unique SectionID field.  The term of a section is “Fall” or “Spring” for residential courses and “Fall-B”, “Fall-D”, “Spring-B”, “Spring-D”, etc. for online courses.  The year represents a 4-digit year.  All sections are taught by a faculty member, whether the section is offered online or residentially.  A faculty member may not be assigned to teach any classes, however.  This situation occurs when a new faculty member is hired and added to the Faculty table but is not yet assigned to individual classes.

Students can enroll in multiple sections (of different classes, of course), and each section can have multiple students enrolled in it.  Each student receives a grade in every class in which he or she is enrolled.  For undergraduate students, this grade can be “A”, “B”, “C”, etc.  For graduate students, the grade can be “A”, “A-“, “B+”, etc.  Therefore, the grade field must be large enough to accommodate the + or – as needed.  This grade is not entered until the end of the semester or term; however, the student is still recorded as being enrolled in the class from the minute he or she registers for it.   That is, a start date is assigned to the enrollment record as soon as the student registers for the class.  If a student withdraws from a class, the enrollment record is given an end date representing the date of withdrawal, and the grade reflects a “W”.  If the student drops the course before the official “drop/add” date, the record is removed from the table as if the student had never enrolled in it.

Residential faculty members are assigned to offices.  Online faculty members, unless they are on campus, are typically not assigned to offices.  In some cases, faculty members share offices, and sometimes, offices exist but are empty until they can be assigned to faculty members.  Each faculty office is associated with a Building.  There are multiple Room 100s throughout the various campuses, and a room does not have an identity outside of its association with a Building.  The same situation holds for classrooms.  A classroom does not have an identity outside of its association with a building.  Buildings, in turn are not dependent on any other entity.  They are assigned to a particular campus, but they do not derive their identities from the campus.

ZipCodes are stored in the format 24515-0000.  Counting the dash, each zipcode can be as long as 10 characters.  Although it may seem strange now, in a later chapter, we will see why ZipCodes are often broken out into a separate table.

 

 

 

EXAMPLE – How your output should look for each table

Campuses:

CREATE TABLE Campuses

(CampusID int,

CampusName varchar(20),

CONSTRAINT pk_campuses PRIMARY KEY (CampusID))

 

 

Now, it’s your turn….

 

GPA Fix: Online Essay Writing Service from Top Tutors
Save More. Score Better
Pages (550 words)
Approximate price: -

Why Hire a Professional Essay Writer from GPA Fix

Quality Urgent Essays

GPA Fix online platform provides the best essay writing service ever received elsewhere, thanks to our professional essay writers. Experience gained in the 10+ years of being in the assignment writing industry is also an added advantage.

Professional and Experienced Academic Writers

A lot is done to maintain us as the best essay writing service provider. Besides owning a degree from a recognized university, a writer must pass the rigorous tests we take them through before they are considered eligible to offer urgent essay help on our website.

Affordable Urgent Assignment Help

Who said a reliable website should not be cheap also? While it is human nature to associate prices with quality, that does not always work. At gpafix.com, we guarantee you cheap and urgent essay help without compromising the quality.

24/7 Available Urgent Essay Writing Service

Whether it is a ‘write my essay for me cheap’ or ‘edit my essay’ order you want to request, always feel free to reach us at any time of the day. Our experts work in shifts to ensure that you access urgent essay writing services at any time of the day.

Plagiarism Free Essays Online

The punitive measures that come with plagiarized content are so harsh, and we understand that. We consequently ensure to write each client’s papers from scratch no matter how urgent the essay is. To acknowledge borrowed content, our quick writer references and cites the work.

Wow Customer Support 24/7

Our support agents are available 24 hours a day 7 days a week and committed to providing you with the best customer experience. Get in touch whenever you need any assistance.

Try it now!

Calculate the price of your order

We'll send you the first draft for approval by at
Total price:
$0.00

How it works?

Follow these simple steps to get your paper done

Place your order

Fill in the order form and provide all details of your assignment.

Proceed with the payment

Choose the payment system that suits you most.

Receive the final file

Once your paper is ready, we will email it to you.

Our Services

So much stress and so little time? Take care of yourself: let us help you with your tasks. We offer all kinds of writing services.

Essays

Custom Essay Writing Services

No matter what kind of academic paper you need and how urgent you need it, you are welcome to choose your academic level and the type of your paper at an affordable price. We take care of all your paper needs and give a 24/7 customer care support system.

Admissions

Admission and Business Papers

An admission essay is an essay or other written statement by a candidate, often a potential student enrolling in a college, university, or graduate school. You can be rest assurred that through our service we will write the best admission essay for you.

Editing

Editing and Proofreading

Our academic writers and editors make the necessary changes to your paper so that it is polished. We also format your document by correctly quoting the sources and creating reference lists in the formats APA, Harvard, MLA, Chicago / Turabian.

Coursework

Revision Support

If you think your paper could be improved, you can request a review. In this case, your paper will be checked by the writer or assigned to an editor. You can use this option as many times as you see fit. This is free because we want you to be completely satisfied with the service offered.