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.)
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.
Before you begin…a few helpful hints:
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].
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
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.
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.
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.
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.
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.
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.
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(
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
CREATE TABLE Campuses
CONSTRAINT pk_campuses PRIMARY KEY (CampusID))
Now, it’s your turn….
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!
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.
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.
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.
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 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.
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.