Search results for 'CIS-130 Relational Database Management - M4E 2013'

ITCO231 Unit 5 HR Database

Update the database diagram, and generate the data definition language (DDL) for the Contacts table and any others that you wish according to the following diagram. Be sure to define the appropriate relationship between the Contact table and the Employee table.

Add data to your database and validate that your records loaded properly. In tabular format, include 3 rows for each table, making sure that the primary–key and foreign–key relationships are properly applied.
Next, you will insert the 20 rows of data that you identified (using the concepts you worked on identifying the primary and foreign keys), then perform queries using JOIN syntax of the database.
Task 1: Create 3 rows of data for each table ensuring that the referential integrity is valid.
Task 2: Add the 20 rows of data to the appropriate table in your database (using any appropriate method available).
Task 3: SELECT all columns and all rows of the tables. Create a screenshot of each query and output data, and submit them.
Task 4: Write SELECT statements for the following (include a screenshot of the SQL and its execution, including the resulting data):
Display the employee id, contact id, first_name, middle name, last_name, and phone number for all employees.
Rows returned
Display the employee id, contact id, first_name, middle name and last_name, and email address for all employees.
Rows returned
Display the employee id, contact id, first_name, middle name, last_name, phone number, and email address for all employees.
Rows returned
Combine all of the DDL/SQL statements (text only), diagram and screenshots into a single Word document. Submit it for grading.

DBM 502 WEEK 5 Protecting Database

Password protect the database you created for your individual assignment in Week Three.
Create a CRUD matrix to indicate the permissions (read, insert, delete, modify) you would grant to different users of your database.
Create at least two different users and implement their permissions using SQL statements.

Submit the following deliverables to your instructor:
The command(s) to protect your entire SQL Server database
The matrix (categories of users are rows, categories of data are columns, and R, I, D, and/or M in the cells)
The SQL code for creating logins and 2 specific users from your matrix
The SQL code for granting appropriate permissions to your 2 new users

ITCO231 Unit 4 HR Database Tables

Using this diagram, address the following:
Establish the relationships between the tables in the SQL server environment.
Ensure that all primary keys are properly created and that the foreign key columns are defined correctly.
Make sure that the 3 additional tables you added in Unit 3 are also shown and have established primary and foreign keys to appropriate tables.
Update the database diagram, and generate the data definition language (DDL) for the new tables.
Describe what changed in the DDL for each table.
Describe any changes that were needed from the original model to create the relationships.
Submit a consolidated Word document with all screenshots and the DDL.

ITCO231 Unit 3 HR Database Tables

Create the needed tables in the SQL server environment (just tables, no relationships yet).

Use integer (INT) data types for all ID columns.
Use DATE data types for all date-related columns.
Rate related columns should be DECIMAL data types.
All other data types should be VARCHAR.
In addition, create 3 additional tables that could be consistent in a database for a human resources (HR) application, including an appropriate table name and appropriate columns.
Create and submit a screenshot of your database diagram based on the tables, and generate the data definition language (DDL) for each table.
Submit a consolidated Word document with the screenshot and the DDL for all tables (text only, no screenshots).

ITCO231 Unit 2 AdventureWorks Database

Task 1
Click here to download and install the AdventureWorks LT2012 database into the SQL environment. Unzip the file and store the data file and the log file into the Data folder within the folder Program Files (x86)/Microsoft SQL Server/MSSQL11.SQLEXPRESS/MSSQL/DATA .
Choose the appropriate file based on whether you are running SQL Server 2012 or 2008.

Task 2
Generate a database diagram for all the tables in the database.
Include a screenshot of this diagram in your Word document.

Task 3
Write SELECT statements for the following questions:
For each of these statements, include a screenshot of the SQL statements and results. Make sure to include the statement execution, including the resulting data.
Display all columns and all rows from the Customer table.
Display the ProductID, ProductNumber and ListPrice for all rows in the Products table.
Modify query 2 so that the column headings are as follows:
"ID," "Number," and "List Price"
Display the CustomerID, full customer name, and CountryRegion for all customers.
Display the CustomerID, full customer name, and CountryRegion for all customers.
Name the columns "ID," "Name," and "Region" (respectively), and sort the data by the company name.

Combine all of the screenshots into a single Word document, and submit this for grading.

Database Design Final Project Students Get Together SQL Server Database
This exam stresses the concepts of building the database correctly and being able to extract accurate information from It. While the problem is a simple one, logical thought is required to reach the end result.

The Scenario
Well, you have all struggled and made your best efforts. I know that much time and effort went into completing all the work for this course. And now you all need to blow off some steam. So, you all decide to get together to celebrate the conclusion of the course. Everyone wants to celebrate and someone suggests that you get together to go out for a dessert (or two). Now we know everyone has his or her own tastes, and we wouldn’t want to go somewhere that doesn’t have a dessert we like. All the local eateries have certain kinds of dessert they serve. They surely can’t serve every kind of dessert.

You will need to design a database to keep track of all the students in the class. You will also track what kinds of dessert each student likes. A student may like many different desserts, and many students may like the same dessert. Also, we need to keep track of all the restaurants In the area, and all the different desserts they serve. A restaurant may serve many desserts, and many restaurants may serve the same dessert.

Tables
Use DBDL to design the tables (fields, keys, relationships, etc.) necessary to track the information as described above.
Explain any assumptions. Make sure all table are in 3NF and the correct relationships exist between the tables.

Use the SQL Server Management Studio to create the database, tables, relationships, and diagram based on the DBDL.
After the Database is created, you will create the six queries listed below Save the queries as Ql. Q2, Q3, Q4, 05 and Q6.

Queries
1. Create the SQL to show what desserts a restaurant serves. (Show all restaurants and each dessert that they serve.)
2. Create the SQL to show what desserts a student likes. (Show all students and each dessert that they like.)
3. Create the SQL to show students who like Apple Pie.
4. Create the SQL to show the NUMBER of restaurants that serve Apple Pie.
5. Create the SQL to show the most popular dessert. (Based on student likes, not restaurants where it is served.)
6. Create the SQL to show all the restaurants that each student can go to and have at least one dessert that they like. I.e. - If a restaurant has a dessert that the person likes, then they can go to that restaurant. (Show all students and restaurants that each can go to.)

Database Diagram
Create the database diagram of your tables.

Submitting Your Final Project:
Create a Folder Called FinalProjet_YourLastNarne. Put the DBDL (create in Word and Save as a .PDF file), Queries and the .mdf and .ldf Database files in the
FinalProjeel Folder, zip the folder and submit it to the assignment.

IT452 Unit 10 Final Project
In this project, you will create an end-to-end solution to meet a business need. This project is worth 200 points.

Scenario
Your good friend, Misty, has a tutoring business in Southern California. It is quite successful. A client will call with a tutoring need, and Misty will look on her Excel spread sheet of tutors for those qualified in the tutoring area the client needs, and who live near the client.
Misty now has approximately 20 freelance tutors registered with her. It has become quite difficult for her to tell what tutors are qualified and live near the client. You have told her you will develop a way for her to get such a list.
She has sent you the TutorList.xls file with the current list of tutors. (This file is available in Doc Sharing.) The Subject column has values of A (tutor is proficient in English and history), B (tutor is proficient in math and science), or C (tutor is proficient in all basic subjects). Hint: You need to create a database that consists of the data in these to files. The DateQualified column has the latest date the tutor was interviewed and checked out by Misty. In addition, she has provided the current term’s client list (Clients.xls). You will need to use BULK INSERT to load the data from the sheets into the tutoring database and write queries that find tutors in the same zip code as the client and provide the tutor in the same zip code in a report. Misty should be able to select the zip code as a parameter from the tutors in the report and the zip code as a parameter from the clients as a parameter to execute the reports.
Misty also requires that she should be able to interact with a Web page, because she does not want to know how to "run database software". This will require creating a web based report that she can access via a web link.
The first thing you should do is list the steps in your plan. What will be the major steps you need to take to accomplish your goal? The first step will be to install SQL Server 2008 Express with Advanced Services on Misty's computer. (She has agreed to that part of the plan.) The last step will be to create a report that asks for parameter values for a stored procedure or query at run time. This does not literally need to be done but should be a step in the plan if you advise her that it is needed on her machine.
For the purpose of this project, you will use your computer to work out the steps and create the report.
The first item to place in your final project document is your outline of major steps that need to be accomplished. Label this section:

1. Outline – 20 points
<Put your plan step list here.> (The outline will be worth 20 points.)
Then, for each step after the first (the installation step), make a section in your project, e.g.

2. <Whatever Step 2 is in your plan outline> - 80 points
In that section, state how you accomplished that step and/or give the code you used. Enough detail should be given so a knowledgeable person could completely recreate what you did. Show at least some of the results you obtained when you tested to see whether your step had been accomplished. (For
queries, 3 or 4 lines of output, and stating the number of lines returned is sufficient.) If a screen shot is the best way to demonstrate the results, include it in your project. Please use ALT+PrintScreen so that only the in-focus or active window, not the entire desktop, is copied.
[Note: You are starting with step 2, because step 1 of your plan is the installation, and it will be assumed that you know how to do that.]
[Hint: One of your steps should be the creation of a stored procedure that uses a variable for the zip code and determines which tutors live in the same zip code as the client. But, this step will be carried out in #3 below so you might want to letter your steps to avoid confusion.

3. Create the query or stored proc. that will produce the result set for the report - 40 points (Creation and Alter)
Create Procedure – 20 points
Alter Procedure – 20 points
The query / stored procedure that generates the result set for the report will be worth 20 points each. You then will also Alter the Stored Procedure one time and execute it. You should determine that it gives the desired results before configuring your report. Hint: The SELECT query portion of the stored procedure will be the query you use in your report datasource when you get to the portion of the design wizard (if you use the wizard instead of manually creating everything).

4. [The last step.] Configuring the Report and the Report Parameters – 60 points
Query for Report 20 points
Configuring Parameters – 20 points
Configuring/Designing 2 reports – 20 points
The final 60 points will be for the last step in your plan – configuring the report and the parameters for the report. The tutor subject choices should be A, B, or C. The date selection should be from the DateQualified values, with a default value of the earliest DateQualified. The customer zip code must be one of those in the TutorList. [Hint: The latter two parameter items require creation of additional data sets, one for the DateQualified values, and one for the zip code values. Hint: Each of the datasets should use a SELECT query as well to provide the data for the parameter. Look at what you did in Project 9 as an example. The DateQualified data set will have datetime data type. Each parameter will become a drop down on your report.
You should place four screen shots under this final step in your project document.
1. For the parameter that specifies Misty's choice of a tutor that resides in the same zip code as the client show one such client/tutor example.
2. For the parameter that specifies Misty's choice of minimum DateQualified, show how you have configured the Available Values for that parameter.
3. Show the Report Data pane of BIDS, showing all parameters and all data sets with their fields.
4. Deploy the report. Run the first report that does not contain parameters for zip code 91016. This one should show only the tutors available for that zip code. Run the second report with parameters for a customer zip code of 91803 for the report with parameters showing which client they qualify to work with, what their skill set is, and date equal to the earliest DateQualified value. Paste the screen shot of this report into your project document.
Because the entire project involves sequential steps, if you become hung somewhere in the middle and cannot proceed, you may send details to your instructor, and receive a hint in exchange for an appropriate point "cost" or deduction. If you cannot figure out what query you need to create the report, the instructor will send it to you, on request from you, for a "cost" of 20 points so you can move on.
Your screen shot of the report without parameters should look similar to this on the Report BIDS screen. It should be for ZipCode 91016 regardless to subject and qualified date:
Your screen shot of the report without parameters should look similar to this on the Report Manager screen:
Your screen shot of the report with parameters should look similar to this on the Report BIDS screen It should be for ZipCode 91803 regardless to subject and qualified date:
Your screen shot of the report with parameters should look similar to this on the Report Manager screen:

IT452 Unit 8 Programmable Objects II

1. Write a stored procedure called sp_GetCompanyName. This procedure requiring a join should get the customer name (not individual) and the city the customer gets items shipped to for those customers that have items shipped to Madrid (this is a hint on the table it should be joined with in the query). Execute the stored procedure and submit results with query.
Returns 2 rows

2. Write a stored procedure called sp_GetEmployees to dynamically retrieve the city that each employee lives in when it is executed. In addition, please return the lastname and firstname. Execute the procedure and submit the code and results for employees living in London.
Returns 4 rows  

3. [TSQLFundamentals2008 and tempdb]  Scenario: You want to test a stored procedure that will delete the oldest orders. You will run your tests in the tempdb database.

a. Create an exact copy of the TSQLFundamentals2008.Sales.Orders table in the tempdb database (as table tempdb.dbo.Orders) by using SELECT INTO.
The tempdb.dbo.Orders table should have 830 rows.

b. In tempdb, create a stored procedure named dbo.pDeleteOldestOrders that (1) determines the earliest year and month of orderdate values in the Orders table, and then (2) deletes all orders with an orderdate in that year and month. (Hint: Use YEAR and MONTH functions in subqueries to determine the year and month of the oldest order. Place these values in variables, then use the variables in the WHERE clause of your DELETE statement.)
Should show 22 rows affected

4. Use Northwind (create one if one does not exist) and Master  Create a rollback (prevent changes if attempt to change occurs and prints a system message and the custom message using RAISERRORThese Procs may not be altered or dropped!) Trigger called NoTouchDaProc that prevents any stored procedure from being altered or dropped in the database (Hint: in the database tells you which type of trigger).
Submit code and printscreen and drop the trigger after getting results.

5. Use the tempdb and using SELECT INTO create a table called Person using all of the data from the Adventureworks.Person.Contact Table.
You should have 76 rows of individuals with the LastName Carter and 88 rows of individuals with the LastName Johnson before you begin.
1. Create a Trigger called PersonTrigger that works on data after it is inserted into the table and checks for a change to the LastName field. If there is a change to the LastName field print to the screen 'You might have modified the LastName column'. Otherwise have it print 'The LastName column is untouched.' (Hint: IF Else would work here). Make print screens of successful completion and copy and paste your code into your submission.
2. Change the LastName of the person with the contactID of 32. This should update 1 row and fire the PersonTrigger with the correct message. Make print screens of successful completion and copy and paste your code into your submission.
3. Alter the PersonTrigger to check the Inserted and Deleted tables (those that get created when data is inserted or deleted for tables/rows included within a trigger) so that that the trigger now looks in the Inserted and Deleted tables for data updated in the LastName field of the person table. Here is what the SELECT clause should look like: SELECT D.LastName + ‘ changed to ‘ + I.LastName. Complete the balance of the query including the required JOIN between Inserted and Deleted. At this point you should now have 89 people with the LastName Johnson. Make print screens of successful completion and copy and paste your code into your submission.
4. Change anyone with the LastName of Carter to Johnson. This should fire the altered trigger and use the new SELECT clause to report the results to the screen with the number of rows changed to Johnson. 89 rows should be changed from Carter to Johnson. Make print screens of successful completion and copy and paste your code into your submission.

6. [TSQLFundamentals2008 and tempdb]  Scenario: You want to archive deleted orders. You will create a DML trigger to accomplish writing all columns of a deleted order and the time the deletion occurred to an OrdersArchive table. You will test this trigger in the tempdb database.
a. "Reset" the dbo.Orders table in the tempdb database by dropping the table (DROP TABLE tempdb.dbo.Orders; ) and then re-running your code from step a. of the previous problem.
b. Create the tempdb.dbo.OrdersArchive table by executing the following code:
USE tempdb;
GO
CREATE TABLE [dbo].[OrdersArchive]
(
[orderid] [int] NOT NULL CONSTRAINT PK_OrdersArchive PRIMARY KEY,
[custid] [int] NULL,
[empid] [int] NOT NULL,
[orderdate] [datetime] NOT NULL,
[requireddate] [datetime] NOT NULL,
[shippeddate] [datetime] NULL,
[shipperid] [int] NOT NULL,
[freight] [money] NOT NULL,
[shipname] [nvarchar](40) NOT NULL,
[shipaddress] [nvarchar](60) NOT NULL,
[shipcity] [nvarchar](15) NOT NULL,
[shipregion] [nvarchar](15) NULL,
[shippostalcode] [nvarchar](10) NULL,
[shipcountry] [nvarchar](15) NOT NULL
[whenarchived] [datetime] NOT NULL
   CONSTRAINT DF_WhenArchived DEFAULT CURRENT_TIMESTAMP
);

c. Create a trigger named dbo.tdArchiveOrders that writes rows deleted from dbo.Orders to the dbo.OrdersArchive table. Note that the when archived column should contain the current datetime value for the deleted row.
d. Execute the dbo.pDeleteOldersOrders stored procedure that you wrote in the previous problem.

In your Assignment document show:
• The code that creates the dbo.tdArchiveOrders trigger
• The contents of the messages tab of the query window after executing the stored procedure

IT452 Unit 7 Programmable Objects

1. Create a variable for the region column, birthdate column and empid column.

2. Create and use a variable to pull the category name for categoryID 8. Write a separate query to return the output.

3. Write an IF ELSE element that checks for the current month and prints ‘This Month is November’ if it is November or prints ‘This Month is not November’ if the month is not November.
Prints This Month is November if you enter 11

4. Write a WHILE Loop that returns ProductName, CategoryID and UnitPrice and compares the CategoryID to 4. As long as it is less than 4 it should print the product name, categoryid and unitprice.
12 rows for Category 1
12 rows for Category 2
13 rows for Category 3

5. Write a script that performs the following tasks:
a. Define a variable to hold the maximum database ID value.
b. Set that variable equal to the maximum database ID value.  To determine the maximum database ID value, use the database_id column of the sys.databases table in the master database.
c. Use PRINT to print the value of the variable. Result: [Note:  PRINT writes to the Messages tab of the query window.]
[Note:  Your result may vary.]

6. Create a cursor that updates only to and from the ProductName column of the Production.Products table and print that column out. Returns all of the productnames upon successful execution

7. Create a table variable called work table with the following structure. Insert the row of data listed and execute the batch.
Worktable
PK int Primary Key
Col1 int Not Null
Insert 1 row with the following data:
1, 101
Execute the batch
Returns 1 row

8. Create a global temporary table called GlobalTempWork with the following structure
GlobalTempWork
Test nvarchar primary key,
Col1 int
Insert 1 row with the following data
10
Execute the batch to return the 1 row