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

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

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

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

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

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

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

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

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

9. 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….