database test
app | 代做quiz | sql | 代写database – 这是一个关于sql的题目, 主要考察了关于sql的内容,是一个比较经典的题目, 涉及了app/sql/database等代写方面, 这个项目是quiz代写的代写题目
Available until Jun 26 at 11:59pm Time Limit 105 Minutes
Instructions
####### This quiz was locked xx:xx pm.
Attempt History
####### Attempt Time Score
LATEST Attempt 1 103 minutes 58.9 out of 100
####### ^ Correct answers are no longer available.
Score for this quiz: 58.9 out of 100
####### Submitted Jun 26 at 9:42pm
####### This attempt took 103 minutes.
E IS 4420 Midterm Exam
####### 1 page of handwritten notes is allowed.
####### You will use LucidChart for the final question. Be sure to log into LucidChart before starting the exam.
####### Aside from LucidChart, no other websites are allowed.
PartialPartial Question 1 2 / 3 pts
Data is the raw facts captured & stored in the
database such as the numbers, strings, and dates.
Business analytics is derived from processing it (the prior item)
in some way including sorting, aggregating, or building
statistic models.
Finally, you gain knowledge by combining the first 2
items with real-world experience about the domain.
####### Answer 1:
####### Answer 2:
####### Answer 3:
####### Data
####### Business analytics
####### Knowledge
IncorrectIncorrect Question 2 0 / 2 pts
Match the item on the left with the appropriate item on the
right.
Metadata (^) Data created & stored b User Data (^) Data that describes dat
Question 3^2 / 2 pts
Choose the model that correctly implements the below
Business Rules:
People can be considered Customers even if they have not
placed any orders, but ideally they will place a lot of
Orders.
Each Order must be placed by 1 and only 1 Customer.
Question 4^1 / 1 pts
Choose the image that corresponds with this relationship
description:
One Mandatory to Optional Many
Question 5^1 / 1 pts
Choose the image that corresponds with this relationship
description:
One Mandatory to Mandatory One
Question 6^1 / 1 pts
Choose the image that corresponds with this relationship
description:
One Mandatory to Mandatory Many
IncorrectIncorrect Question 7 0 / 1 pts
Choose the image that corresponds with this relationship
description:
Many Optional to Mandatory Many
Question 8^1 / 1 pts
Choose the image that corresponds with this relationship
description:
Many Mandatory to Mandatory Many
Question 9^1 / 1 pts
Choose the image that corresponds with this relationship
description:
One Mandatory to Optional One
PartialPartial Question 10 1 / 4 pts
Match the abstract description on the left with the appropriate
equivalent physical object on the right.
Entity Type (^) Column Entity Instance (^) Row Entity Attribute (^) Relationship Entity Set (^) Row
Question 11^1 / 1 pts
Entity Type or Entity Instance?
(^) Entity Instance (^) Entity Type
IncorrectIncorrect Question 12 0 / 1 pts
Entity Type or Entity Instance?
(^) Entity Type (^) Entity Instance
PartialPartial Question 13 2 / 3 pts
Match the item on the left with the appropriate item on the
right.
Entity Integrity (^) Eat at least 3 servings o Referential Integrity (^) Each Foreign Key value Domain Integrity (^) All values in a single co
PartialPartial Question 14 3 / 5 pts
Match the item on the left with the appropriate item on the
right.
Composite Key (^) An combination of 2 or Candidate Key (^) The Candidate Key sele Primary Key (^) An attribute or combina
Foreign Key (^) An attribute or combina Secondary Key (^) An attribute or combina
IncorrectIncorrect Question 15 0 / 2 pts
Jimmy has built a conceptual data model with a M:N
relationship, denoted below.
Jimmy is now building the physical data model. How should
Jimmy denote the M:N relationship?
Question 16^1 / 1 pts
In relational databases, NULL means:
(^) Zero (0) (^) Zero (0) or an empty string (”). (^) An empty string (”) (^) The absence of a value.
Question 17^1 / 1 pts
The __________ of an attribute it the feasible values it could
possibly have.
domain
Question 18^5 / 5 pts
Match the item on the left with the appropriate item on the
right.
####### Required
Not NULLable
Optional (^) NULLable Atomic (^) Cannot be divided any Composite (^) Consists of multiple me Derived (^) Not stored, but calculat
Question 19^3 / 3 pts
You’re gathering requirements for a database that will support
a Customer Relationship Management (CRM) system from
the VP of sales at a hotel chain. The VP explains to you that
each customer must provide at least 1 email address, but they
want to capture as many email addresses as each customer
provides.
How should you implement this business requirement?
You should create 5 columns in the CUSTOMER table called Email1,
Email2, Email3, Email4, and Email5 because most customers will have
fewer than 5 email addresses.
You should create a column called Emails and store all email addresses
that each customer provides as a comma-separate list in the column.
You should create a 1:M relationship between the CUSTOMER table and
a weak entity that stores the email addresses. The primary key of the
weak entity should consist of CustomerID and Email.
For each email address, you should duplicate the rest of the Customer's
information and have 1 row per email address.
Question 20^3 / 3 pts
Jill is gathering requirements for an app that will be used to
collect information of patients when they show up for their
appointments at medical clinics. Among the attributes the app
needs is the Age of each patient.
How should Age be implemented?
The application should require the Age of each patient to be entered and
stored in the PATIENT table as an INTEGER column. Each year after
the information was entered, an automated process will run to increment
each patient's age by 1.
The application should require the Age of each patient to be entered and
stored in the PATIENT table as a VARCHAR(10) column. This way, a
date or a number can be entered, thus making the data collection more
dynamic.
The application should require the Birth Date of each patient to be
entered and stored in the PATIENT table in a DATE column. Age can be
calculated as a Derived Attribute at any time by comparing the patient's
birth date with the current date.
The application should request the age of each patient to be updated
upon each visit to the clinic and stored in a TINYINT column since this
data type goes up to 127.
Question 21^2 / 2 pts
Relationship Strength is determined by how the Primary Key
of the child entity is defined.
Match the item on the left with the appropriate item on the
right.
Weak Relationship (^) Exists when the Primar Strong Relationship (^) Exists when the Primar
IncorrectIncorrect Question 22 0 / 3 pts
Select the item that does not describe a Weak Entity.
An entity called EMPLOYEE with 1 row per employee, no foreign keys,
and a primary key consisting of EmployeeID.
An entity called ORDER_LINE with a primary key consisting of OrderID
and ProductID.
An entity called SUPPORT_CALL that logs phone calls between call
center employees and customers with a primary key consisting of
EmployeeID, CustomerID, and Date.
An entity called PRICE_HISTORY that tracks the history of price
changes over time with a primary key consisting of ProductID and Date.
IncorrectIncorrect Question 23 0 / 1 pts
The process of identifying Supertypes & Subtypes and
splitting them out is called __________.
(^) Specialization (^) Subtyping (^) Decomposition (^) DeNULLification
Question 24^2 / 2 pts
Rows in a table can sometimes be grouped into
subcategories of the primary entity type. It becomes
appealing to create Subtypes of the main Supertype table if
certain conditions are true.
Mark all of the valid reasons you would want to separate a
table into a Supertype / Subtype relationship.
When a subcategory of rows has a relationship with another table not
shared by the rest of the rows.
When a subcategory of rows has NULLability not shared by the rest of
the rows.
When a subcategory of rows has 1 or more unique columns not shared
by the rest of the rows.
Question 25^2 / 2 pts
The column in the Supertype that specifies which Subtype will
have matching rows is called:
(^) Subtype Discriminator (^) Disjointness Constraint (^) Primary Key (^) Completeness Constraint
IncorrectIncorrect Question 26 0 / 4 pts
Mark all the correct statements about the Enhanced Entity-
Relationship Diagram (EERD) below.
A patient must be an OUT_PATIENT or an IN_PATIENT, but cannot be
both.
(^) The Subtype Discriminator is PatientType. A patient doesn’t have to be an OUT_PATIENT or an IN_PATIENT, they can just be a PATIENT. The relationship between DOCTOR and PATIENT is inherited by OUT_PATIENTs and IN_PATIENTs. (^) This model allows for Generalization. A patient can be an OUT_PATIENT and an IN_PATIENT at the same time. (^) NURSE_SHIFT has a surrogate primary key. (^) PATIENT_ROOM has a surrogate primary key.
PartialPartial Question 27 1 / 3 pts
Mark all the correct statement about the Enhanced Entity-
Relationship Diagram below.
(^) All VEHICLES must be either a CAR or a TRUCK. (^) The Subtype Discriminator is Make + Model. Some VEHICLEs can be a CAR and a TRUCK at the same time (think El-Camino, science yo!) (^) This model allows for Generalization. (^) Some VEHICLEs are not a CAR or a TRUCK. (^) The Subtype Discriminator is VehicleType.
IncorrectIncorrect Question 28 0 / 3 pts
Gustavo works for a mortgage company and is gathering
business requirements for a Loan Origination System (LOS).
He has identified 4 types of employees and is implementing
Supertypes & Subtypes in the ERD.
How should Gustavo implement the Subtype Discriminator for
the below model?
Gustavo should create 1 VARCHAR(2) column called EmployeeType and
use:
CC to indicate Call Center Employees
LO to indicate Loan Officers
ME to indicate Marketing Employees
MG to indicate Managers
Gustavo should have the sql code check each Subtype for the
existence of a row with a matching EmployeeID, and when one is found
he will know it is that type of employee.
Gustavo should create 1 Boolean column per Subtype and call them:
IsCallCenterEmployee
IsLoanOfficer
IsMarketingEmployee
IsManager
For each row, each of the columns will indicate if there is a matching row
in the relevant Subtype.
Question 29^1 / 1 pts
Associative Entities are used to implement M:N relationships.
(^) True (^) False
IncorrectIncorrect Question 30 0 / 1 pts
It’s typical to use a Surrogate Primary Key in Associative
Entity tables when:
You have duplicate rows in your data and the Surrogate Primary Key
creates uniqueness.
It's not typical to use Surrogate Primary Keys in Associative Entity tables.
The Associative Entity table has a relationship to a child table and you
don't want to implement a Composite Foreign Key.
Question 31^1 / 1 pts
A given value of AttributeA functionally determines
AttributeB. Which attribute is the Determinant?
(^) AttributeB (^) AttributeA
IncorrectIncorrect Question 32 0 / 3 pts
Suppose you have a Relation that has a Primary Key and no
Repeating Groups. It has a Composite Key with a Partial
Functional Dependency.
How would you resolve this Partial Functional Dependency?
You would move the column(s) that are Partially Functionally Dependent
to a parent table. The column(s) they were Partially Functionally
Dependent on serves as the Foreign Key to the parent table.
It is not possible to have Partial Functional Dependencies with a
Composite Primary Key. Therefore, no action is necessary.
You would create a 1:M relationship between the initial table and a new
table and place the columns that are Partially Functionally Dependent on
the many side.
You would move the column(s) that are Partially Functionally Dependent
to a child table. The column(s) they were Partially Functionally
Dependent on serves as the Primary Key to the child table.
Question 33^3 / 3 pts
Suppose you have a Relation that has an atomic Primary Key
and no Repeating Groups. All Non-Key columns are
Functionally Dependent on the Primary Key, but it has a
Transitive Functional Dependency.
How would you resolve this Transitive Functional
Dependency?
You would create a 1:M relationship between the initial table and a new
table and place the columns that are Transitively Functionally Dependent
on the many side.
You would move the column(s) that are Transitively Functionally
Dependent to a parent table. The column(s) they were Transitively
Functionally Dependent on serves as the Foreign Key to the parent
table.
Transitive Functional Dependencies can only exist with a Composite
Primary Key, and therefore cannot exist in the table as it has an atomic
Primary Key.
You would move the column(s) that are Transitively Functionally
Dependent to a child table. The column(s) they were Transitively
Functionally Dependent on serves as the Primary Key to the child table.
IncorrectIncorrect Question 34 0 / 3 pts
Suppose you have a Relation that has an atomic Primary Key
and a Repeating Group. All Non-Key columns are
Functionally Dependent on the Primary Key.
How would you resolve the Repeating Group?
You should break each value of the Repeating Group into atomic cells
and duplicate all the information in the rest of the columns, 1 row per
value of the repeating group.
You should create a 1:M relationship between the initial table and a child
table and place the values of the Repeating Group in the child table on
the many side.
You should create a 1:M relationship between a parent table the initial
table and place the values of the Repeating Group in the parent table on
the 1 side.
Question 35^2 / 2 pts
Which normal form does the below relation meet?
(^) 1NF (^) 3NF (^) 2NF (^) BCNF
IncorrectIncorrect Question 36 0 / 2 pts
Which normal form does the below relation meet?
(^) 3NF (^) 2NF
(^) 1NF (^) BCNF
IncorrectIncorrect Question 37 0 / 2 pts
Which normal form does the below relation meet?
(^) 3NF (^) 2NF (^) 1NF (^) BCNF
Question 38^5 / 5 pts
You & your friends have created a startup company called
ScoreTrackr, and are building an app for sports organizations
to use to track sports games. You have created the ERD
below, and now you need to convert it into Relational Notation
as an intermediate step towards creating the actual SQL DDL
statements.
Mark all of the below snippets that are correct fragments of
the Relational Notation that would result from the above
ERD. Choose 5.
TEAM_MEMBER ( TeamMemberID, TeamID, FirstName, LastName )
PRIMARY KEY ( TeamMemberID )
FOREIGN KEY ( TeamID ) REFERENCES TEAM ( TeamID )
DIVISION ( DivisionID, Name )
PRIMARY KEY ( DivisionID )
DIVISION ( DivisionID, Name )
PRIMARY KEY ( DivisionID )
FOREIGN KEY ( DivisionID ) REFERENCES TEAM ( DivisionID )
COACH ( TeamMemberID, Salary )
PRIMARY KEY ( TeamMemberID )
FOREIGN KEY ( TeamID ) REFERENCES TEAM ( TeamID )
PLAYER ( TeamMemberID, Contract, Position )
PRIMARY KEY ( TeamMemberID )
FOREIGN KEY ( TeamMemberID ) REFERENCES
TEAM_MEMBER ( TeamMemberID )
COACH ( TeamMemberID, Salary )
PRIMARY KEY ( TeamMemberID )
FOREIGN KEY ( TeamMemberID ) REFERENCES
TEAM_MEMBER ( TeamMemberID )
TEAM ( TeamID, DivisionID, CoachTeamMemberID, TeamName )
PRIMARY KEY ( TeamID )
FOREIGN KEY ( DivisionID ) REFERENCES DIVISION (
DivisionID )
FOREIGN KEY ( CoachTeamMemberID ) REFERENCES COACH
( TeamMemberID )
Question 39 10.9 / 15 pts
Create An Entity-Relationship Diagram using
the Business Requirements outlined below.
Important Notes:
####### ENTITIES are denoted in bold & all caps.
####### Attributes are in italics.
####### Watch out for M:N relationships, Multivalued attributes, and
####### Supertypes & Subtypes.
####### The first 3 entities have already been modeled. Please start
####### building your ERD by adding them first.
Business Requirements (requirements in orange have
already been modeled):
####### Each RACE has a RACE_CATEGORY, which is either Mountain
####### Biking or Trail Running.
####### For each RACE, the RaceID , RaceCategoryID , VenueID ,
####### RaceName , RaceDate , and RaceFee must be tracked.
####### Each RACE is hosted by a VENUE. A VENUE can host many
####### different RACEs. For each VENUE, the VenueID , Name and
####### Address ( StreetAddress , City , State , and ZipCode ) are collected.
####### RACEs have many RACERs, and each RACER will ideally
####### participate in a lot of RACEs. Therefore, a M:N relationship
####### exists between RACE and RACER.
####### Each RACERs FinishTime needs to be tracked so the RACERs
####### can be ranked at the end of the RACE.
####### When RACERs create their profile before their first race, they
####### must provide an EmailAddress , and ideally as many Email
####### Addresses as possible. Therefore, Email is a multivalued
####### attribute.
####### In addition to emails, you also need to track the RacerID , and
####### FullName ( FirstName & LastName ) of each RACER.
####### Because DirtRacers organizes Mountain Biking and Trail
####### Running races, RACERs can be TRAIL RUNNERs, MOUNTAIN
####### BIKERs, or both.
####### For TRAIL RUNNERs, each RACER is issued a BibNumber on a
####### flexible yet durable piece of fabric that can be pinned to their
####### shorts or shirt.
####### For MOUNTAIN BIKERs, each RACER is issued a PlateNumber ,
####### which is a stiff plastic plate that must secured to their
####### handlebars.
####### BibNumbers & PlateNumbers are considered completely
####### different attributes, as each RACER can have both, and the
####### numbers are not mutually exclusive.
ERD Starting Point
####### ^ 39.png
(https://utah.instructure.com/files/133104902/download)