Search results for 'Week'

CS362 Structured Query Language for Data Management Week 3 IP Select Statements:

Provide select statements to satisfy the following data requests:
1. List all active male students assigned to Advisors 1 or 3 (Fred Stone or Jack Simpson). (Where Clause - Filtering 3 different things: Active, Male Students, Adivsors)
2. Provide a list of all students without a biography. (focusing Null Biography)
3. What classes are in the English department? (Where Clause)
4. Create a list of all students and their advisors. Sort by the advisor’s name and then the student’s name. Include the student’s birth date, gender, and GPA. (join's statement combine the two tables students and class tables: Order by clause like birthday, gpa,etc)
5. How many students were born in the 1980s? (Aggregate using count expression)
6. Write a query to show the average GPA by gender. (Aggregate using count expression)
7. Provide a list of all advisors and the number of active students assigned to each. Filter out any advisors with more than 1 student. (Combine lines 4-6)

Copy and paste the work into your Key Assignment document and include screen shots of each step, describe what you did for each step and paste in the actual SQL text used to perform each step. Include an explanation as to how and where these queries or others like them can be used in your final system. Upload your document to the Submitted Tasks.

CS362 Structured Query Language for Data Management Week 4 IP Extend the Design

Using your Week 1 IP as a starting point, extend the design to accommodate degree programs. The new design should incorporate the following functionalities:

1. A degree has a name and description.
2. What degree is the student working towards? A student can only work on one degree at a time.
3. What classes are necessary to obtain a specific degree?
4. Provide the DDL script to add the tables for the new design changes. Include an updated diagram that shows all the tables in the system.
5. Write the DML script to insert 3 test records in each of the tables in the system. This data will be necessary to write the queries in the next assignment.

Copy and paste the work into your Key Assignment document and include screen shots of each step, describe what you did for each step and paste in the actual SQL text used to perform each step. Upload your document to the Submitted Tasks section.

CS362 Structured Query Language for Data Management Week 5 IP Functions Stored Procedure and Indexes

This assignment requires you to take your extended design from Week 4 IP and add proper indexes, a function, and a stored procedure. Your changes will provide functionality used by the teachers for screens such as a grade book. Be sure any code is properly formatted and has appropriate comments.

Part 1: Function
Write a user-defined function (UDF) that calculates a student's GPA for a given time frame. Inputs are StudentId int, ClassStartDateStart datetime, and ClassStartDateEnd datetime. The output should be the student's GPA for all classes that were taken between ClassStartDateStart and ClassStartDateEnd. Also, supply the script to call this new function, passing it parameter values of your choice.

Part 2: Stored Procedure
Write the DDL script to make a stored procedure that returns data needed to display a grade book screen for a professor. The only input for the stored procedure is a ClassId. Outputs need to include student names and grades for all assignments as well as a calculated overall grade for the class for each student. Provide an example calling this new stored procedure, passing it parameter values of your choice. Include a screenshot of the output.

Part 3: Indexes
Provide a list of suggested indexes and the DDL script to create them. Include an explanation of the purpose of indexes and how you made your decision for the fields to include in your list of suggested indexes.

Copy and paste the work into your Key Assignment document and include screen shots of each step, describe what you did for each step and paste in the actual SQL text used to perform each step. Upload your document to the Submitted Tasks.

CS251 Fundamentals of Database Systems Week 4 IP The Microsoft Access Database

Using Microsoft Access create the tables and relationships defined in your data model. Your Microsoft Access Database at this point should include the following:

The Tables as defined in your Entity Relationship Diagram.
The Relationships between the tables.
3 Defined Queries. One of the queried needs to be created to show the data from the Many-to-Many relationship (or include three tables) originally identified from the initial requirements.
Take a screenshot of the ERD and the Queries.

Add the Screenshots to the section in your template titled: The Microsoft Access Database.

Final Project Part IV: Using MS Access

1. Based on the design created in Week 2, create the physical design of the database using MS Access.
 Create tables that represent the entity that you created in Week 2.
 Create the required columns with the appropriate data types.
 Specify an appropriate primary key for the table.
 Specify constraints for columns wherever applicable

2. Enter at least ten records in the database.

3. Query the database.
Create queries to filter records based on various conditions that you can think of. Some queries you can create include:
 If your table has an Age column, then create a query to view the records of students of the same age.
 If your table has an Address column, then create a query to view the records of students living in the same area.

4. Check if there is a need to redesign the database.

5.Create entry forms for the database.
 Create a form that helps you enter records in the table you created.
 Ensure that the constraints you applied in the table are also checked  in the form.
 Suggest a strategy to ensure that the primary key values are not repeated.

6.Create a report that displays records of all students.

Design the database, enter data, run the queries, create the forms, create the reports and submit your database to the Dropbox. In a Word document, share your experience about this design and implementation process and discuss the need and scope for redesigning the database.

DBM/380 Week 3 ERD Smith Consulting in Access

Individual Database Creation Project

The following assignment is based on the database environment chosen and discussed in the Week Two Individual Assignment.

Create a Microsoft® Visio® diagram to create a detailed Entity Relationship Diagram(ERD) using the data specifications noted in the Week Two Individual Assignment. Make any necessary changes provided in your faculty’s feedback.

Create a Microsoft® Access® database to create the preliminary database tables, columns with data types, primary keys, and relationships.

DBM/380 Week 4 Normalization of the Smith Consulting ERD

The following assignment is based on the database environment chosen and created in the Week Three Individual Assignment.
Your database project must meet the following assessment requirements:

Design and develop a database using professional principles and standards.
Provide a logical and physical design of the database.
Use relational database software application to develop database.   
Provide an entity relationship diagram.
Normalize the database.
Generate and provide test data.

Use a Microsoft® Visio® diagram to normalize the ERD to third normal form (3NF).

Use the Microsoft® Access® database created in Week Three to create a minimum of 10 rows of test data in each table. Also, create at least one query that joins two tables and returns values from both tables.

Note. Only the Microsoft® Visio® diagram must be normalized to the 3NF. The 3NF is not required for a Microsoft® Access® database.

Submit the ERD and final database to the appropriate Assignment link.

INF322 DATABASE MANAGEMENT Week 5 Final Project Customer Service Database

The Final Project consists of building a customer service database to be submitted by the final day of the course.

Focus of the Project
1. Use the Access database you created in Week 3: Customer Service Database

2. Output 3 queries using SQL code and not the standard access view:
A query on all contacts in the customers table showing first name, last name, address, e-mail address and three additional fields from the table.
An update query that changes one or more customer records but not all records.
A delete query that removes one or more customer records but not all records.

3. Using SQL commands, add a new field called Birthday to the Customers table. Populate some of the fields with data.

4. Report on Customers in Database showing Names, State, and Phone Numbers.

5. Create a report showing contact name and birthdays. Do not show records without a birthday.

6. Analyze the data populated in the Database to show referential integrity. This includes a statement showing what the affected tables in each dataset are and how they are impacted in the case of an addition of data or a deletion of data.

7. All data and screen shots should be included in a Word document with a brief explanation of the actions you performed.

CMIS 320 Project 1 Mom and Pop Johnson video store ERD

This project allows students to demonstrate their skills in the area of designing relational databases to satisfy specific business rules and requirements. The deliverables for this project include an Entity Relationship Diagram and detailed documentation describing the database design and structure.

Project 1 Details:

In this project you will be provided a set of specifications to create an entity-relationship diagram and design accompanying table layout using sound relational modeling concepts and practices. The relationships between the entities and the attributes for the entities will be identified and described. This database will provide the foundation for the follow-on project. The following paragraphs provide the background and summary of the business requirements.

You are a database consultant with Ace Software, Inc. and have been assigned to develop a database for the Mom and Pop Johnson video store in town. Mom and Pop have been keeping their records of videos and DVDs purchased from distributors and rented to customers in stacks of invoices and piles of rental forms for years. They have finally decided to automate their record keeping with a relational database.

You sit down with Mom and Pop to discuss their business, and watch their operation for about a week. You discover quickly that a video and a DVD are both copies of a movie kept in a separate plastic case that is rented out. They have several copies of each movie they rent; therefore there are several videos and DVDs for each movie title. You learn that in their inventory they have several thousand videos and DVDs, which they get wholesale from about a half dozen distributors. The video and DVD prices to them are based on the quantity of their shipment and the past business they have done with each company.

The price of a DVD for a movie might be different than the price of a video for the same movie, even from the same distributor. Each distributor provides different types of movies (e.g., suspense, horror, mystery, comedy, etc.). A single distributor may provide several different types of movies in both video and DVD format. It is possible to obtain the same movie from multiple distributors, and at different wholesale prices.

Each video and DVD has a unique identification number that Mom and Pop assign in their inventory, in addition to the distributor's serial number for the item. Each movie also has a unique identification number Mom and Pop assign in addition to the title, and any movie IDs the distributors use in their electronic catalogs. Distributors provide electronic catalogs to Mom and Pop and the information from these catalogs must be included in the database.

Mom and Pop need to record when a video or DVD is rented, when a video or DVD is returned, and all customer charges such as late and damaged fees, failure to rewind fees, and taxes. They need a report of which videos are returned late because there are standard and late charges. On occasion there are discount prices for certain movies or types of movies. Customers want to rent movies based on actors or actresses, running length, type of movie, rating, year released, the director, and the academy awards won (by the movie, the actors, the actresses and/or the directors). Customers also want to know how many videos they have rented in the last month, year, and so forth. Mom and Pop need to keep only basic information on customers in their database, such as name, address, telephone numbers, etc.

There must be no limit to the number of video and/or DVD copies of a movie that Mom and Pop can have in their inventory. Video/DVD ID numbers, movie ID numbers, and distributor ID numbers for videos, DVDs, and movies are all different. Also, each movie must be able to have an unlimited number of actors, actresses, directors, and academy awards (i.e., Oscars). Other types of awards (e.g., Golden Globe, People's Choice, etc.) are not of interest for this application. The rental of equipment, sale of videos, DVDs, popcorn, etc., is not to be kept in the database.

Using this information, you should:

1. Determine and list your entities. Then describe fully the relationships between entities via pairs of sentences that indicate the two components of the total relationship in both directions between the entities. Relationships may be unary, binary, or ternary with respect to entities. You should not have any many-to-many relationships.

2. Begin the database logical design by identifying the entities, relationships between entities, and entities' attributes as we have done in this course. Use the same entity/relationship diagram (ERD) notation as used in class for entities, attributes, and relationships. Sketch your ERD by hand or a drawing program (e.g., Visio, PPT, SQL Modeler …) on one single 8-1/2" x 11" page (8-1/2" x 14" maximum), labeled "Mom and Pop Johnson Video Store Database E/R Diagram." Your ERD should not have any many-to-many relationships between entities. Make sure that all relationships between entities are one-to-many to facilitate the construction of relational database tables. 

3. Complete the logical database design and start the physical database design by creating metadata (i.e. documentation) that describes the table(s) created from each entity and the column(s) created from each attribute in the ERD. Attributes should be self-describing. Particular attention will be given to the proper specification of all primary key (via "PK") and foreign key (via "FK") columns in the table layouts. These should match your ERD exactly.  Begin these descriptions on a page labeled "Proposed Database Tables and Columns based on E/R Diagram." All tables must be 3rd Normal Form. Indicate any and all assumptions that were made.

4. Make sure your work is neat and legible.

Submissions:

1. You should submit a well-organized Word document that includes your entity list, ERD, and database design description. This document should also include all assumptions and your lesson learned during your project.

2. Submit in your WebTycho portfolio in the Project 1 area before the due date. Please contact the instructor before the due date if you have any questions or concerns.