IS-4420 Database Fundamentals
homework | assignment代做 | 作业lab | database – 本题是一个利用database进行练习的代做, 对database的流程进行训练解析, 包括了database等方面, 该题目是值得借鉴的lab代写的题目
lab 2, Logical & Relational Data Modeling
20 points
Setup
1 - Log into LucidChart and create a blank document.
2 - Search for the Entity-Relationship (ERD) library, pin it, and remove all other libraries.
Note
- You are encouraged to collaborate on homework assignments with your peers.
- If you get stuck, attend tutoring office hours.
- Send questions via email to the Me & the TAs.
- Assignments will take time, start them early.
- Class attendance will be rewarded by getting hints in class about the assignments.
1: Building Logical ERDs (4 points)
Build a Logical ERD using the below business description.
EquestrianZone, a family owned business that sells horse products, was founded in 1976 and has grown to have 8 locations throughout the western United States. Ned Picket, the son of the founder and current owner, has grown tired of the physical paperwork. During a date with a really nice cowgirl he met on FarmersOnly.com, he learned that a database would be just what he needs to get rid of that darned paperwork once and for all.
Ned heard the U of Utah has a great Information Systems program and has contacted you to build the data model that will be used for the database. During your conversation with Ned, you learn the following Business Requirements (rules).
As the company has grown, Ned has observed how different regions have different characteristics. For example, costs to procure certain products vary per region, and logistics costs differ as well. Therefore, Ned wants to categorize stores into regions. Each region will have at least 1 store, and possibly many stores. Each store will belong to only 1 region. For regions, Ned wants to track the RegionID, Name, and a Description. For stores, Ned wants to track the StoreID, Address (StreetAddress, City, State, Zip), and the EmployeeID of the employee who manages the store.
Each store must have multiple employees to operate. Each employee works at just 1 store. For employees, Ned wants to track EmployeeID, FirstName, LastName, SSN, HireDate, and Title. Some employees are store managers, but most are not. Each store is managed by 1 employee.
2: Building Logical ERDs (4 points)
Build a Logical ERD using the below business description.
Calls-R-Us is a call center company that provides phone call services for businesses that dont have a call center of their own, or periodically need to expand beyond their capacity. The businesses they facilitate calls for provide scripts for their generic call center agents to use to interface with each customer.
Calls-R-Us employees who take calls are called, agents. An agent can have calls with many customers, and each customer can have calls with many agents. For each phone call that occurs, Calls-R-Us also needs to know which business the call is for. For example, a customer named Margaret may call and speak with an agent named Phil to inquire about her Comcast invoice. Therefore, the phone call can be considered the intersection of the agent, the customer, and the company that Calls-R-Us is taking the call for.
For each agent, Calls-R-Us wants to track the AgentID, FirstName, LastName, Title, and HireDate. For each customer, Calls-R-Us wants to track the CustomerID, FirstName, LastName, and PhoneNumber. For each business, Calls-R-Us wants to track the BusinessID, Name, ContactName (ContactFirstName, ContactLastName), and PhoneNumber. For each call, Calls-R-Us wants to track the Date, BeginTime, and EndTime of the call. Of course, Calls-R-Us wants to know which agent, customer, and business the call was related to as well.
Assume that:
- Agents and businesses may be added to the database before any calls are associated with them.
- Customer information is added to the database when they make their first call to the call center.
3: Building Logical ERDs (4 points)
Build a Logical ERD using the below business description.
DonorZone is a nonprofit organization that helps wealthy donors donate funds to a wide variety of causes for people in need.
When a donor donates money, they choose which cause they want to donate to. A donor can donate to 1 or many causes, and each cause can have 0 or many donors. Therefore, there is a M:N relationship between donors and causes. People are considered donors when the make their first donation. Causes can be listed in the database without having any donations.
For donors, DonorZone wants to capture the DonorID, Name (FirstName, LastName), Address (StreetAddress, City, State, Zip), Phone Number(s) and Email Address(es). Each donor must provide at least 1 email & phone number. If a donor provides more than 1 phone number or email address, DonorZone wants to capture all of them. Therefore, phone number & email address are multivalued attributes of donors.
For causes, DonorZone needs to track the CauseID, Name, and a Description of the cause.
For donations, DonorZone needs to track which donor donated to which cause, amount donated, and the date of the donation. Assume each donor can donate to each cause no more than once per day.
4: Relational Algebra (4 points)
Use the following images to answer the question.
1 - Create the table that would result from:
FirstName LastName Major
Jimmy Erickson Information Systems
Sally McDonnel Accounting
Nelly Robinson Marketing
Rohit Carlisle Information Systems
Carlos Evans Business Analytics
2 - Create the table that would result from:
FirstName LastName CourseID Semester
Jimmy Erickson 4430 Fall 2019
Nelly Robinson 4460 Fall 2019
Nelly Robinson 4440 Fall 2019
Rohit Carlisle 4415 Summer 2020
Rohit Carlisle 4420 Summer 2020
5: Review (4 points)
Answer the following questions:
1 - Describe how Composite Attributes are implemented and provide an example.
A composite attribute is an attribute that is made up of several bits of data. For example, a
persons name is composed of the first and last name. To implement composite attributes, one
must store the individual bits of the composite attribute instead of storing the actual composite
attribute. For instance, an address can be represented in a table by the individual bits like street
address, city, state, and zip code. Below is an example of how an address composite attribute
may be represented.
Example:
STUDENTS
StudentID
Name{FirstName, LastName}
Address{StreetAddress, City, State, Zip}
Becomes:
STUDENTS
StudentID
FirstName
LastName
StreetAddress
City
State
Zip
2 - Describe how Multi-Valued Attributes are implemented and provide an example.
Multi-valued attributes are attributes that can store more than one record of data. For example,
the email attribute may allow users to add more than one email. This type of attribute is usually
implemented by forming a separate table that contains a single attribute referenced by the
foreign key. This way, one can add multiple attributes of the same kind.
Example:
USERS
UserID
FirstName
LastName
{emails}
Becomes two tables:
USERS
UserID
FirstName
LastName
3 - Describe how M:N relationships are implemented and how they differ from other types of
relationships.
A many-to-many relationship occurs when multiple records in one table are associated with
multiple tables of another table. To resolve this, one creates an intermediary table between the
two tables to normalize the records. For example, a customer can place many orders and an
order can be placed by many customers. To solve this, a table say, order details can be put in
between that contains the customer id, order id and other relevant details such that there is a
one to many relationship between customer and order details and a one to many relationship
between the order and order detail table.
M:N relationships differs from other relationships as it is not supported by many Relational
Database Management Systems and that it can lead to excessive redundancy if not well
managed.
4 - Describe the difference between weak & strong relationships and how they are denoted in a
data model.
The difference between a strong and weak relationship is that, in a strong relationship, the child
table contains the parents primary key as a component and cannot exist independent of the
parent while in a weak relationship, the child table does not contain the parents primary key as
one of the components. A strong relationship is denoted by a solid line whereas a weak relation
is denoted by a dashed line.
Submission
Save this file as a PDF with the naming convention Lab_2_FirstName_LastName.pdf and upload it to the assignment page in Canvas.
EMAILS
EmailID
UserID
EmailAddress