jcdavidson CSIS 325 Lab 4.docx - CSIS 325 – Lab 4(SQL CREATE TABLE Before beginning this lab be sure you have successfully downloaded and installed

jcdavidson CSIS 325 Lab 4.docx - CSIS 325 – Lab 4(SQL...

This preview shows page 1 - 3 out of 16 pages.

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.) 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. 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
Image of page 1

Subscribe to view the full document.

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.
Image of page 2
Image of page 3
  • Fall '15

What students are saying

  • Left Quote Icon

    As a current student on this bumpy collegiate pathway, I stumbled upon Course Hero, where I can find study resources for nearly all my courses, get online help from tutors 24/7, and even share my old projects, papers, and lecture notes with other students.

    Student Picture

    Kiran Temple University Fox School of Business ‘17, Course Hero Intern

  • Left Quote Icon

    I cannot even describe how much Course Hero helped me this summer. It’s truly become something I can always rely on and help me. In the end, I was not only able to survive summer classes, but I was able to thrive thanks to Course Hero.

    Student Picture

    Dana University of Pennsylvania ‘17, Course Hero Intern

  • Left Quote Icon

    The ability to access any university’s resources through Course Hero proved invaluable in my case. I was behind on Tulane coursework and actually used UCLA’s materials to help me move forward and get everything together on time.

    Student Picture

    Jill Tulane University ‘16, Course Hero Intern

Ask Expert Tutors You can ask 0 bonus questions You can ask 0 questions (0 expire soon) You can ask 0 questions (will expire )
Answers in as fast as 15 minutes