Search results for 'im 300'

MIS582 iLab 3: Database Construction Using Access

iLAB OVERVIEW
Scenario and Summary
In this assignment, you will learn to create an Access database from a given ERD. To complete this assignment, you will need to be able to run Access 2010, either through Citrix or installed on your workstation or laptop.

Deliverables
Name your Access database file using Lab3_, your first initial, and your last name (e.g., Lab3_JSmith.accdb). Create and save your Access database file. When you are done, submit your database to the Course Project Dropbox.

iLAB STEPS

STEP 1:
Review the ERD below to understand the entities, attributes, primary keys, and relationships that you will create in your Access database.

STEP 2:
• Run Access 2010, either via Citrix or from Visio 2010 installed on your workstation.
• Select the blank database icon in the main window.
• Save the file with a name containing Lab3_, your first initial, and your last name (e.g., Lab3_JSmith.accdb). In Citrix, you will need to click the computer icon in the Save As window to see the different drives. Be sure to save the file to a local drive so it will be on your workstation.
See the tutorials above for instructions on how to perform the following steps in Access 2010.

STEP 3:
Add tables to the Access database.
• Add a table for each entity listed in the provided ERD diagram.
• Add a column for each attribute listed in the provided ERD diagram.
• Select a primary key for each table as indicated in the provided ERD diagram.

STEP 4:
For every column in every table, update the data type as needed to enforce the domain constraints of the data.
• Dates should have a date data type.
• Surrogate keys should be autonumbered.
• Numeric data should have a numeric data type.
• Character data should have a character data type.

STEP 5:
Draw relationships between your entities.
• Selection Relationships under Database Tools. Move all your tables into the Relationship window by dragging them in or by using the Show Tables pop-up window.
• Second item
o Add the relationship between the tables in your database.
o Enable referential integrity on the relationship.
o Enable cascade updates on the relationship.

STEP 6:
Add at least two rows of data to each table in your database. Use any values you like for each of the columns. Remember that you must add data to parent tables before adding data to child tables, because referential integrity is enabled.

STEP 7:
Set the following column constraints in your database.
• Student first and last name cannot be a null value.
• Course credit hours must be between one and four.
• Course name must be unique and cannot be a null value.
• Instructor first and last name cannot be a null value.
• Grade must be one of these values: A, B, C, D, F, I, W, or E. W signifies withdrawn and E signifies enrolled.

STEP 8:
When you are done, save the file on your local hard drive and upload it to the Course Project Dropbox. Your file should have the following filename format: Lab3_FirstInitialLastName.accdb.
For instructions on how to copy files between the Citrix server and your local machine, watch the iLab tutorial, Copying Files from Citrix, located in the iLab menu tab under Course Home.
Note!
Submit your assignment to the Dropbox located on the silver tab at the top of this page.

Learn More

MIS582 iLab 4 Forms and Reports

i L A B O V E R V I E W
Scenario and Summary
In this assignment, you will learn to create and save forms and reports in a provided Access database. To complete this assignment, you will need to be able to run Access 2010.

Deliverables
Name your Access database file using Lab4_, your first initial, and your last name (e.g., Lab4_JSmith.accdb). Create and save your Access database file. When you are done, submit your database to the Course Project Dropbox.

i L A B S T E P S
STEP 1
• Download the Lab4_FormsReports.accdb database from Doc Sharing.
• Rename the Lab4_FormsReports.accdb database with a name containing Lab4_, your first initial, and your last name as the file name (e.g., Lab4_JSmith.accdb).

STEP 2:
• Run Access 2010, either via Citrix or from Visio 2010 installed on your workstation.
• Open the database you renamed in Step 1.

STEP 3:
Reports
Create a report for each of the following requirements. Reports may be created using the wizard or an SQL query. Reports should follow the formatting guidelines given in the next step. Reports should be named as indicated below (e.g., Report1, Report2, etc.).
1. Report1: Show Instructor information (Instructor_ID, First_Name, Last_Name) grouped by Approved Course_No.
2. Report2: Show student information (Student_ID, First_Name, Last_Name, Status, Zip) grouped by Major.
3. Report3: Show student information (Student_ID, First_Name, Last_Name, Major) grouped by Zip code. Zip codes should be in increasing order.
4. Report4: Show student information (First_Name, Last_Name) grouped by Course Number and Course Name. Course Number should be in in increasing order.
5. Report5: Show student information (Student_ID, First_Name, Last_Name, Address, Zip) grouped by Status. Students should be in alphabetical order by Last_Name and;then First_Name.

STEP 4:
Report Format
• Reports should display two or more child rows for each parent row. For example, Report1 should display each Course_No with instructors who are approved to teach the course grouped under it. In this case, the parent row would be the Approved Course_No with Instructor listed as child rows underneath each parent row
• There should be at least two child rows of data for each parent row. Add data to the tables in order to have reports show at least two child rows per parent.

STEP 5:
Form1: Create a form based on an SQL query.
• Create and run an SQL query that displays Student Information (Student_ID, First_Name, Last_Name, Address, Zip).
• Save the query in the database named Form1Query.
• Create a form based on Form1Query. While Form1Query is selected, go to the Create menu and select Form to build the basic form.
• Select the form and switch between the views (Forms, Layout, and Design) to change the properties or the form’s appearance as desired.
• Save the form in the database named Form1Query.

STEP 6:
Form2: Create a form using the Form Wizard.
• Invoke the Form Wizard as shown in the tutorials above.
• Using the Form Wizard, create a form showing instructor information (Instructor_ID, First_Name, Last_Name, Office_No).
• Switch between the views (Forms, Layout, and Design) to change the properties or the form’s appearance as desired.
• Save the form in the database named Form2Wizard.

STEP 7:
Form3: Create a form using a master detail relationship. A master detail relationship is simply a 1:N relationship between two tables. Use subforms to format the form in a split data entry form as shown in Figure 1-9. You may use SQL queries or the wizard or both to create your form and subform.
• Create a master form showing student information (Student_ID, First_Name, Last_Name, Address, Zip, Major, Status).
• Create a detail Subform showing Student_ID, Class_ID, and Student_Grade.
• For each student displayed, the student’s classes and grades should be displayed in the detail form.
• Switch between the views (Forms, Layout, and Design) to change the properties or the form’s appearance.
• Save the form in the database named Form3MaterDetail.

STEP 8:
When you are done, save the file on your local hard drive and upload it to the Course Project Dropbox. Your file should have the following filename format: Lab4_FirstInitialLastName.accdb.

Learn More

Create a complete ERD in Crow’s Foot notation that can be implemented in the relational model using the following description of operations. Hot Water (HW) is a small start-up company that sells spas. HW does not carry any stock. A few spas are set up in a simple warehouse so customers can see some of the models available, but any products sold must be ordered at the time of the sale. • HW can get spas from several different manufacturers. • Each manufacturer produces one or more different brands of spas. • Each and every brand is produced by only one manufacturer. • Every brand has one or more models. • Every model is produced as part of a brand. For example, Iguana Bay Spas is a manufacturer that produces Big Blue Iguana spas, a premium-level brand, and Lazy Lizard spas, an entry-level brand. The Big Blue Iguana brand offers several models, including the BBI-6, an 81-jet spa with two 6-hp motors, and the BBI-10, a 102-jet spa with three 6-hp motors. • Every manufacturer is identified by a manufacturer code. The company name, address, area code, phone number, and account number are kept in the system for every manufacturer. • For each brand, the brand name and brand level (premium, mid-level, or entry-level) are kept in the system. • For each model, the model number, number of jets, number of motors, number of horsepower per motor, suggested retail price, HW retail price, dry weight, water capacity, and seating capacity must be kept in the system. In addition, for this problem, use Excel to make a “Components of the ERM” table similar to table 4.4 on page 134. Learn More

GetWell, Inc. is a leading pharmaceutical organization. The organization wants to store all its customer and order details in an MS Access database. You need to perform the following tasks:

Create a database called GetWell.
Create two tables, Customer and OrderDetails, with the following information:

Customer
FieldName Type Size
CustomerID Text 5
CustomerName Text 25
CustomerAge Text 3
CustomerAddress Text 30

CustomerID will be the primary key in the Customer table.

OrderDetails
FieldName Type Size
OrderNumber Text 5
CustomerID Text 5
OrderDate Date/Time
OrderValue Number
Discount Number

OrderNumber will be the primary key in the OrderDetails table.

Enter following records in the Customer table:

CustomerID CustomerName CustomerAge CustomerAddress
C0001 Debbie Howe 28 123, Florida
C0002 Ed Young 33 New York, 234
C0003 Tom Wilkins 47 A-234 New Jersey
C0004 XXXXX XXXXXe 76XXXXXSuite
C0005 Ken Burton 52XXXXXNorth
C0006 XXXXX XXXXX 65 53, N. Dakota
C0007 XXXXX XXXXX 29 655, Wisconsin
C0008 John Barrett 55 2234, Georgia

Enter the following records in the Order table:

OrderNumber CustomerID OrderDate OrderValue Discount
O1207 C0003 10/1/2004 10500 10
O2222 C0008 4/30/2005 26790 15
O2657 C0001 11/23/2004 50000 15
O3412 C0003 2/3/2004 222 0
O4532 C0004 3/27/2005 12320 10
O4578 C0006 6/8/2004 1110 5
O4778 C0001 7/8/2004 3378 5
O5732 C0007 4/15/2005 98700 15


Write SQL statements for the database you have created to do each of the following:

Display details of all the customers whose age is greater than 45 years.
Display the names and addresses of all the customers who have placed orders with a value more than $10,000.
Display the names of customers along with the values of the orders they placed after March 01, 2005.
Create an INSTEAD OF trigger to display the message "You cannot update Order Value", if a user tries to update the OrderValue field in the Order table.

Perform all the above activities using MS Access.

Learn More

MIS562 SQL Database assignment

1. Show a list of all employee names and their department names and the employees for each department. Be sure to show all departments whether there is an employee in the department or not. Use an outer join.

2. Select all employee names and their department names. Be sure to show all employees whether they are assigned to a department or not. Use an outer join.

3. Write a query that that performs an inner join of the grade, student, and grade_type tables using ANSI SQL 99 syntax.

4. Write a query that that performs an inner join of inner join of the grade, student, and grade_type tables using the Oracle inner join convention.

5. List all the zip codes in the ZIPCODE table that are not used in the STUDENT or INSTRUCTOR tables. Use a set operator.

6. Write a SQL statement using a set operator to show which students enrolled in a section that are not enrolled in any classes. Exclude students with student id less than 300.

Learn More

COMP353/1 Assignment #3

Exercise #1 (10 points)
Here are the two sets of FDs for R {A, B, C, D, E}.
S = {A->B AB->C D->AC D->E} T = {A->BC D->AE}
Are they equivalent?

Exercise #2 (10 points)
Consider the following decomposition of the table ENROLLMENT in two tables Student and Course.

Table ENROLLMENT
StudentID StudentName CourseName Credits
1111111 William Smith COMP218 4
2222222 Michel Cyr COMP353 4
3333333 Charles Fisher COMP348 4
4444444 Patricia Roubaix COMP353 4
2222222 Paul Paul COMP352 3
5555555 Lucie Trembaly COMP354 3

Table Student
StudentID StudentName Credits
1111111 William Smith 4
2222222 Michel Cyr 4
3333333 Charles Latan 4
4444444 Patricia Roubaix 4
2222222 Paul Paul 3
5555555 Lucie Trembaly 3

Table Course
Credits CourseName
4 COMP218
4 COMP353
4 COMP348
4 COMP353
3 COMP352
3 COMP354

Question: Is this decomposition lossless? Justify.

Exercise #3 (15 points)
Using the Functional Dependencies,
F = {A → BC ; CD → E ; B→D ; E→A}

a) Compute the closure of F (F+).
b) Is true / false : F ⊨  E → BC?
c) Provide the minimal cover Fc (min(F)) using steps shown in the class.
d) List of the candidate keys for R

Exercise #4 (15 points)
Consider the relation R(S, N, R, C, J, H, L) and the set of dependencies.
F = {S, N  C ; J, H, C  L ; J, H, L  S, N, R ; S, N, R  J, H, L}.
Prove that F ⊨ J, H, S, N  R using the Armstrong’s axioms?

Learn More

Develop an Art Museum ERD for DBM/380 Week 3

List the data specifications (must include a minimum of three entities with attributes)

Has to be on An Art Museum that needs to track the artwork, artists, and locations where the art is displayed or stored within the museum If you Don't have Visio then Do a ERD in Access. Need this last week. Make sure the Database flows.

Use a Microsoft® Visio® diagram to create a detailed ERD using the data specifications noted in the Week Two Individual Assignment. Make any necessary changes provided in your faculty's feedback.

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

Learn More

DBM/380 Week 4 Normalization of the Art Museum ERD

The following assignment is based on the database environment chosen and created in that last request

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 that you just created for me, 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.

Learn More

Guidelines

The work consists of the design, implementation and population of a small database system that will form the basis of the report to be handed in.

You will be expected to work on your own and should submit deliverables arrived at independently using your own knowledge and effort.

Sopwith Coach Hire Database

Sopwith Coach Hire Ltd is a leading provider of coach and minibus hire in West London. The company was founded in 1982 and it has continued with steady expansion over the past 30 years. The company operates a fleet of vehicles which are available for hire. The majority of the company’s clients are local schools within West London that use the vehicles for school trips. . However, vehicles can also be booked for private functions.

The Director has requested the creation of a database system to assist in the more effective and efficient running of the company. The Director has provided the following description of the current system. 

System Overview

Customers

A vehicle can be booked by customers and a customer can book many vehicles.

Details of customers need to be recorded, which include name, address, email address and contact number.

Vehicles

The company has a large number of vehicles that range from a standard six seater MPV to a 49 seater coach.

Table 1 (appendix A) lists the company’s fleet of vehicles. The company has more than one model type of vehicle. The hourly rate for hiring each vehicle is also displayed in this table.

Booking

The company wishes to record details of all bookings made by their customers. This will include detailed information of each trip a vehicle is booked for.

Each customer is charged the hourly rate for each vehicle (you may assume that no discounts are given for long term hire).

Employees

The company employs a large number of employees and details of each member of staff need to be recorded. These details include name, address, home telephone number, date of birth, gender, job description, national insurance number and current annual salary. The majority of employees are drivers but the company also has a number of administrative staff that takes the bookings.

The database must be able to handle driver assignments. Each hired vehicle requires a driver. A second driver is required in the following situations; (1) If the driver cannot have a break of not less than 45 minutes after each 4.5 hours of driving, or (2) if the driver cannot have a 10 hour break after driving a maximum of 9 hours driving in a day.

Licence

Each driver must have a valid driver’s licence with no penalty points. Details of each driver’s licence needs to be recorded. This will include the licence number and the expiry date.

In order to drive a small passenger-carrying vehicle with nine to 16 passenger seats, a driver must have a D1 code on their licence. To drive any bus/coach with more than eight passengers, a driver must have a D code. Some drivers have only a D1 code and are therefore only allowed to drive the minibuses. Other drivers have both codes on their licences and can be used for any of the company’s hired vehicles. 

Tests 

The company operates a program that involves random alcohol or drug testing. Any driver may be required to take either the alcohol or drug test or both.

Sopwith Coach Hire Ltd is required to keep a complete record of all test types, dates and results for each driver.

 

C Tasks and Grading

1. Create an entity-relationship model (using UML notation). Specify the cardinality ratio and participation constraint of each relationship type. State any assumptions you make when creating the ER model (if necessary). (30 marks)

2. Produce a Data Dictionary for the ER diagram in part (1). (25 marks)

3. Implement the set of relational tables created in part (2) using Oracle.

You must include your CREATE TABLE statements in the report. (10 marks)

4. Populate the tables with relevant data.

A screen shot of the populated tables must be included in the report. (10 marks)

5. A full discussion of the constraints used, covering the three types: entity, referential and column. Explain why you used them and how they ensure that the integrity and consistency of your data is maintained. (10 marks)

6. Write 3 queries to demonstrate that your database meets the data requirements of the system. You have to work out what you consider to be the best 3 queries that demonstrate the database meets the data requirements of the system. In your queries you are expected to use a range of SQL queries. ***

A screen shot of the query output must be included in the report.

*** If you produce 3 queries that are simply SELECT * FROM, then you will receive no marks. (15 marks)

Learn More