ERD

CIS 355 Term Project Part I For your term project, you are expected to design and implement a relational database to meet the requirements described in the PCI Warranty Call Center Case. Deliverables Part I - Project Design Document This document should have the following components: 1. A conceptual ER model/diagram of PCI’s data requirements. The diagram should include all relevant entities, attributes and relationships. For each entity, specify the identifier (primary key). Specify relationship names and cardinality constraints. Indicate which attributes are required, composite, multi- valued, and/or derived (Note: by default, an attribute is assumed to be optional, simple, single-valued and not derived). Indicate which entities are associative. Follow consistent naming conventions for entities and attributes. Use modeling/diagramming software to create the ER Diagram. 2. A list of the normalized relations (the logical model) and their attributes. For each relation, primary and foreign keys should be clearly indicated. Note: Use the format that we will be discussing in class for presenting your logical model. 3. A list of assumptions (if any) made about the information requirements presented in the case. Note: the assumptions should be reasonable and should not contradict the facts of the case. 4. A data dictionary that defines the metadata for the logical model. The data dictionary should include: the definition of each relation and attribute; the primary and foreign keys in each relation; attribute data types and lengths; and whether attributes are optional or required. Organize the data dictionary alphabetically by relation name. Assessment Part I deliverables will be evaluated based on the completeness and correctness/accuracy of the conceptual and logical data models, and of the supporting documentation (i.e., data dictionary, assumptions (if any)). If any of the deliverables are hand-drawn/written, your submission will not be graded. Submit the deliverables as one or more files. Include your name and title of the project on every page of the documents you submit. Learn More

MIS582 iLab 2: Data Modeling Using Visio

iLAB OVERVIEW
Scenario and Summary

In this assignment, you will learn to create a physical database model in Visio from business requirements. To complete this assignment, you will need to be able to run Visio 2010, either through Citrix or installed on your workstation or laptop.

Deliverables
Name your Visio file using Lab2_, your first initial, and your last name (e.g., Lab2_JSmith.vsd). Create and save your database model in your Visio file.

iLAB STEPS

STEP 1:
Read the following business requirements closely to determine the entities and relationships needed to fulfill the requirements. The nouns in the paragraph will tell you the entities that will be needed. The verbs in the paragraph will help you determine the relationships between the entities.
Muscles Health Club Database Requirements:
The Muscles Health Club needs a database to keep track of its members, their personal trainers, and the fitness classes they are taking. Employees can act as personal trainers for members. However, only certified employees can act as personal trainers. A member can work with only one personal trainer at a time. Members can take multiple fitness classes. Fitness classes are taught by employees who can teach multiple classes. Fitness classes are taught in one of the classrooms at one of Muscles Health Club’s several locations. Each fitness classroom is designed for a different type of class (e.g., spinning, aerobics, water aerobics, weight training, etc.). It is necessary to track what fitness classes are being held in each of the different Muscles Health Club locations.

STEP 2:
• Run Visio 2010 either via Citrix or on your workstation.
• Click on the Software and Database Template group in the main window.
• Double-click on the Database Model Diagram Template to open a new file.
• Save the file with a name containing Lab2_, your first initial, and your last name as the file name (e.g., Lab2_JSmith.accdb). 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.

STEP 3:
Add an entity for each entity you identified in the requirements.
• Drag the entity icon onto the drawing area in Visio.
• In the Database Properties window, add a physical name to identify it.

STEP 4:
For each entity, create a list of attributes you think would be useful to describe the entity.
• Select an entity in the drawing area of Visio.
• In the Database Properties window, select the Columns category.
• Use the table to add your attributes to the selected entities.
• Select one of the attributes to be the primary key (PK).

STEP 5:
Set the diagram to use crow’s feet notation.
• On the Database tab, in the Manage group, click Display Options.
• In the Database Document Option dialog, select the Relationship tab.
• Select the Crow’s Feet check box, and then click OK.

STEP 6:
Draw relationships between your entities.
• Drag the relationship icon onto a blank part of the drawing area.
• Connect the two ends to each of the two entities in the relationship. The parent entity must have a PK defined. The entity will be outlined in bold red lines when it connects to one end of the relationship.

STEP 7:
Set the cardinality of your relationships.
• Select a relationship line in the drawing area that is connecting two entities.
• In the Database Properties window, select the miscellaneous category.
• Select the cardinality for the selected relationship.

STEP 8:
When you are done, save the file on your local hard drive and upload it to the Course Project Drop box. Your file should have the following filename format: Lab2_FirstInitialLastName.vsd.
Submit your assignment to the Drop box located on the silver tab at the top of this page.

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

Types of Relationships and Sample Databases

Part 2
Come up with an original example of a one to many unary relationship. Create an example model of this relationship using the MySQL Workbench data modeler.

Part 3
Come up with an original example of a ternary relationship. Create an example model of this relationship using the MySQL Workbench data modeler.

Part 4
Using MySQL Workbench – create a data model for on Adult Baseball league which supports all of the following data requirements:
Teams
    A team has a unique number associated with it
    Team Name
    Team City
    Each team has more than one player associated with it

Players
    Each Player has a unique number
    A player may play or have played for more than one team
    The number of years and batting average is tracked for each team the player played for.

Coaches
    In this league, all coaches are also players.
    A Team can have more than one coach.

Bats
    A team can have multiple bats
    Each bat is identified by a unique serial number.

For each of the three models above, you should export your model as a single page PDF and submit the resulting files.

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

CIS515 Assignment 2: Database Systems and Database Models

The Strayer Oracle Server may be used to test and compile the SQL Queries developed for this assignment. Your instructor will provide you with login credentials to a Strayer University maintained Oracle server.

Imagine that you have been hired as a consultant to assist in streamlining the data processing of an international based organization that sells high-end electronics. The organization has various departments such as payroll, human resources, finance, marketing, sales, and operations. The sales department is the only department where employees are paid a commission in addition to their yearly salary and benefits. All other departments compensate their employees with a yearly salary and benefits only. Commission is paid by multiplying the employee’s commission rate by the total amount of product units sold. You have access to the following data sets:

Employee (EmpNumber, EmpFirstName, EmpLastName, CommissionRate, YrlySalary, DepartmentID, JobID)
Invoice (InvNumber, InvDate, EmpNumber, InvAmount)
InvoiceLine (InvLineNumber, InvNumber, ProductNumber, Quantity)
Product (ProductNumber, ProductDescription, ProductCost)
Department (DepartmentID, DepartmentDescription)
Job (JobID, JobDescription)

Write a two to three (2-3) page paper in which you:

Design a query that will allow the finance department to determine the commissions paid to specific employees of the sales department for the month of December. Note: You will need to generate the tables described above (Employee, Invoice, InvoiceLine, Product, Department, and Job) in order to compare and validate your code. Validated query code must be part of your paper.
Compare the code of the query you designed in Question one (1) to one that would show how much total compensation is paid to each employee for the same month.
Determine and explain the factors necessary to ensure referential integrity.
Create an object-oriented model to show how the tables are interrelated through the use of graphical tools such as Microsoft Visio, or an open source alternative such as Dia. Make sure that you are able to show the relationship types such as 1:M, 1:1, or M:1. Additionally, remember to include the determined factors from the previous assignment requirement. Note: The graphically depicted solution is not included in the required page length.
Identify which data components are the entities and attributes, and the relationship between each using an object representation diagram through the use of graphical tools such as Microsoft Visio, or an open source alternative such as Dia. Note: The graphically depicted solution is not included in the required page length.
Describe how Big Data could be used to assist in the productivity and forecasting of the organization’s products and resources.

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