Database

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.

Learn More

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

Learn More

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.

Learn More

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

Learn More

POS 410 Week 2 Table Queries SQL for Business

Create two tables using the specifications listed.
• Use the database built in last week’s assignment to create the two tables and associated columns listed below.  Table names, data types, and column lengths must be used as specified; column names may be created as desired and appropriate.  Avoiding the use of spaces and special characters in the column names is recommended.
• In the Employee table, create an Employee ID column that generates a unique number for each employee and designate the column as the Primary Key. In the Job table, use the job title column as the table’s primary key.

Table Name = Employee_Tbl
• Employee ID (type= numeric identity) PK
• Last name (type=varchar(30))
• First name (type=varchar(30))            
• Address (type=varchar(30))
• City (type=varchar(30))        
• State (type=varchar(2))
• Telephone area code (type=varchar(3))            
• Telephone number (type=varchar(7))
• Employer Information Report (EEO-1) classification                  (type=varchar(30))
• Hire date (type=date)
• Salary (type=money)
• Gender (type=varchar(1))
• Age (type=numeric)
• Job Title (type=varchar(30))

    
Table Name = Job_Tbl
• Job title (type=varchar(30)) PK    
• Job description (type=varchar(2000))
• Exemption status (type=varchar(1))

Use the SQL INSERT statement to go to the human resources department in the Kudler Fine Foods Virtual Organization. Using information found in the employee files for the La Jolla and Encinitas stores, enter records into the Employee table for the following employees:
• Glenn Edelman
• Eric McMullen
• Raj Slentz
• Erin Broun
• Donald Carpenter
• David Esquivez
• Nancy Sharp

Use the information from the job classifications and descriptions to enter records into the Job Title table for the following titles:
• Accounting clerk
• Assistant manager
• Bagger
• Cashier
• Computer support specialist
• Director of finance and accounting
• Retail assistant bakery and pastry
• Retail assistant butchers and seafood specialists
• Stocker

Hint: Data entered into the job title column of the employee_tbl and job_tbl tables must match exactly, including case, spaces, and so on.  If they do not match exactly, you will not be able to create the foreign key or perform the joins that are required in later assignments.
Deliverables:  check your results by selecting all of the data from both tables (one table at a time).  Copy and paste screen images of your work in a word doc, then post as an attachment in your assignment tab of the classroom.  You may need to resize your windows and take multiple screen shots to capture all of the required information.  The screen images should show the following information:
• Database, table, and column definitions
• SQL statements used to load the data
• Data stored in both tables (display contents after data is loaded)
Refer to the materials forum for the rubric that will be used to grade this assignment.

Learn More

POS 410 Week 1 SQL for Business Individual: SQL Tables

Create an SQL Server database for the Kudler Fine Foods Virtual Organization.

Reference Chapter 5 of this week’s reading material for the instructions and command (create database) that is needed to create the database. 

Database Recommendations (Hint: you will need to create the pos410 subdirectory on the D drive of the Toolwire desktop before  the Create Database command can be successfully executed using these specifications.)

Database Name:  Kudler_FF
Logical File Name: Kudler_FF
Database File Name: D:\pos410\Kudler_FF.mdf
Database Size: 10
Database Max Size: 50
Database Growth: 5
Logical Log Name: Kudler_FF_Log
Log File Name:  D:\pos410\Kudler_FF_Log.ldf
Log Size: 5
Log Max Size: 25
Log Growth 5

NOTE:  The database will not be needed until you start building the tables for week 2, but the sooner the database is built the better.  Waiting until the second week to start building your database could result in week 2 assignments submitted late, due to the work involved in building the database.

Learn More

CTS2437 – Final Exam Provide the SQL statements required to accomplish the following tasks. #1 (10 points) Create a database named FINAL_EXAM that you will then use for all remaining problems. #2 (20 points) Create the tables and appropriate constraints based on the following ER diagram. Use appropriate data types. Note that the size column should only accept S, M, or L. In addition the price column should have values greater than zero. All columns in both tables are required. Catgeory Product C #3 (5 points) Insert 3 rows in the Category table. The db is for a small shoe store, so use appropriate data for the description ( “Men”, “Women”, “Children”) #4 (5 points) Insert 3 Product records for each category in the product table. Use whatever data you see as appropriate. #5 (5 points) Use one statement to increase the price of all products in the Men category by 25%. #6 (5 points) Use one statement to delete all products for the Children category. #7 (10 points) Create and execute a view named EXAM_VIEW that shows all columns from both tables. Use an inner join. #8 (10 points) Create a database trigger named EXAM_TRIGGER that prevents a user from deleting a Product record on Tuesdays. Display an appropriate error message. Make sure to show that the trigger is working properly. #9 (10 points) Create a stored procedure named SP_EXAM that will be used to insert records into the Product table. Make sure to show that the procedure is working properly. #10 (5 points) Remove the EXAM_VIEW object from the database. #11 (5 points) Remove the SP_EXAM stored procedure from the database. #12 (5 points) Remove the EXAM_TRIGER database trigger from the database. #13 (5 points) Remove the FINAL_EXAM database. Learn More

POS/410 SQL for Business Week 4

SQL Reports

Use tables from the Kudler_FF database to write the SQL statements necessary to perform the following data manipulation:
o Increase all employees salaries with the selected EEO-1 classification by 10%.
o Increase all employees salaries by 5%.
o Choose an employee from the Employee table and delete that employee.
Deliverable: Copy the SQL statements and data from both tables showing how data was affected to a Word document. Post the document to the assignment tab of the classroom.

Use tables from the Kudler_FF database to write SQL statements that perform the following computations:
o Calculate the average salary for all employees.
o Calculate the maximum salary for all employees.
o Calculate the maximum salary for exempt employees.
o Calculate the minimum salary for all employees.
o Calculate the minimum salary for non-exempt employees.
Deliverable: Copy the SQL statements and results to a Word document. Post the document to the assignment tab of the classroom.

Use tables from the Kudler_FF database to write the following queries, using the GROUP BY clause in your SQL statements:
o Count the number of employees, grouped by EEO classification.
o Count the number of employees, grouped by exemption status.
o Compute the average salary, grouped by area code and gender.
o Compute average age, grouped by exemption status o Compute the earliest hire date, grouped area code, gender, and exemption status. Deliverable: Copy the SQL statements and results to a Word document. Post the document to the assignment tab of the classroom.

Learn More

ITCO333 Data Modeling and Design Unit 2 Individual Project
Deliverable Length: 1 Word document and 1 .accdb file

Details: Create a SQL Server database called: ITCO333Database. Using Data Definition Language (DDL) and Data Manipulation Language (DML) you will create the below DEPARTMENTS and EMPLOYEES tables. Before you begin creating the new tables, review your tables created in Unit 1 IP to ensure that your database is in third normal form (3NF).

Part 1:
Your ITCO333 database should contain data related to the organizational departments in your Unit 1 chosen topic. Therefore, create a DEPARTMENTS table with the following field specifications:
Field Name Data Type Other Comments and Requirements
Department_ID   int Primary Key
Department_Name nvarcha(50)
Insert at least four records of sample data into the DEPARTMENTS table.

Your ITCO333 database should contain data related to employees in your Unit 1 chosen topic. Therefore, create an EMPLOYEES table with the following field specifications:
Field Name Data Type Other Comments and Requirements
Employee_ID Int Primary Key Last_Name nvarchar(50) Cannot be null.
First_Name nvarchar(50) Cannot be null.
Birth_Date datetime
Employment_Start_Date datetime
Hourly_Pay decia(p[,s]) Must be greater that 0
Department_ID Int Related to the DEPARTMENTS table.
Create a foreign key constraint.
Manager_ID Int Related to the Employee_ID in this table.
Create a foreign key constraint.
Insert at least eight records of sample data into the EMPLOYEES table.

Part 2: Using your Unit 1 ERD, create tables, fields, primary keys and relationship constraints in your ITCO333Database.
Insert your Unit 1 sample data into the newly created tables.

Part 3: Generate a SQL Server Database Diagram.

Additional Requirements: All tables must be in Third Normal Form (3NF). This may require you to normalize your Unit 1 data. Be sure to incorporate Instructor feedback from your Unit 1 IP. Use the following data types:
Integers: int
Decimals: decimal(p[,s])
Strings: nvarchar(50)
Date/Time: datetime

Deliverable: One Word document with:
SQL DDL to create database
SQL DDL to create tables (including EMPLOYEES and DEPARTMENTS tables)
SQL DML to insert data (including EMPLOYEES and DEPARTMENTS sample data)
SQL Server Database Diagram

The aforementioned SQL DML must be in text format (no screen shots).

Learn More