[SOLVED] FIT2094 Databases

INFORMATION TECHNOLOGY
FIT2094 Databases
Week 2 Conceptual Modelling
Please obtain a copy of the Drone case study for this workshop from the week 2 block on Moodle under Workshop Resources header

Copyright By Assignmentchef assignmentchef

Workshop S1 2022

The Database Design Life Cycle
Requirements Definition
Conceptual Design
Logical Design
Physical Design

Requirements Definition
Identify and analyse user views.
A user view may be a report to be produced or a
particular type of transaction that should be supported.
Corresponds to the external level of the ANSI/SPARC architecture.
Output is a statement of specifications which describes the user views particular requirements and constraints.

Staff & Student
Different views of the underlying data

ER Modeling
ER (Entity-Relationship) model developed by in 1976 to aid database design.
Used for conceptual model (ERD).
ER diagrams give a visual indication of the design.
Basic components: Entity
Attribute
Relationship

Conceptual Design
Develop the enterprise data model.
Corresponds to the conceptual level of the ANSI/SPARC
architecture.
Independent of all physical implementation considerations (the type of database to be used).
Various design methodologies may be employed such as UML, ER (Entity-Relationship) Modelling and Semantic Modelling.
ER consists of ENTITIES and RELATIONSHIPS between entities An ENTITY will have attributes (things we wish to record), one
or more of which will identify an entity instance (called the KEY)

ERD Notation
Information Engineering/ /Crows foot
* This is what we will be using

Conceptual Level (ER Model)
RELATIONSHIP
Connects entities on a conceptual model this is the ONLY manner in which entities are connected
Assignment 1A
Collection of Customer(s)
KEY ATTRIBUTE(S)
Instance identifier
NON KEY ATTRIBUTE
Other non-key attributes

Q1. In your group, discuss your pre workshop identification of the Monash Software Entities. How many entities did your group identify:
A. 2 B. 4 C. 5 D. 6

Conceptual Level (Monash Software Entities)

Logical Design
Develop a data model which targets a particular database type (e.g. relational, hierarchical, network, object-oriented, noSQL).
Independent of any implementation details which are specific to any particular vendors DBMS package.
Normalisation technique (see week 4) is used to test the correctness of a relational logical model.

Logical Level (Logical Model Relational)
Assignment 1B

Q2. Is the diagram shown below a valid Conceptual Model?
Be prepared to justify your answer with why you chose this option
A. Yes B. No
C. Depends on how it is implemented in the database

Physical Design
Develop a strategy for the physical implementation of the logical data model.
Choose appropriate storage structures, indexes, file organisations and access methods which will most efficiently support the user requirements (not part of unit).
Physical design phase is dependent on the particular DBMS in use.
ANSI/SPARC internal level.

Physical Level Starting point
The database schema

Important rule for Conceptual Modelling
All that is described in the brief has been included and all that has been included was described in the brief
Every entity, attribute and relationship described in the brief has been included, and
Must not add entities, attributes and relationships which are not included as part of the brief, and
In a real life scenario if there are concerns about features of the brief, discuss with client
For assignments:
your client will be the ed forum
may make assumptions provided they do not violate this rule

CONNECTIVITY/CARDINALITY
In general for Crows Foot notation specific cardinalities are not shown as above eg. (1,4), instead cardinality is depicted via min and max using standard symbols (Inside symbol = min, outside symbol = max)
CONNECTIVITY
one to one
one to many
many to many

Note this is not an acceptable form of a conceptual model in Crows Foot notation (relationship lines cannot join)

Weak vs Strong Entity
Strong entity
Has a key which may be defined without reference to other entities.
For example EMPLOYEE entity.
Weak entity
Has a key which requires the existence of one or more other entities.
For example FAMILY entity need to include the key of employee to
create a suitable key for family
Database designer often determines whether an entity can be described as weak based on business rules
customer pays monthly account
Key: cust_no, date_paid, or
Key: payment_no (surrogate? not at conceptual level)

Weak vs Strong Entity
Note the Crows Foot model shown here has been modified from the text version

Q3.The client indicates that a CLASS is identified by a combination of the the prof_id and the assigned class number for the professor (1st class, 2nd class, 3rd class etc):
prof_id, class_no, class_day, 1, 1, Tue
2, 2, Tue .
This business rule is captured in the provided diagram. Pick the correct statement for this diagram.
A. Both entities are strong entities
B. PROFESSOR is a strong entity, CLASS is a weak entity
C. CLASS is a strong entity, PROFESSOR is a weak entity
D. Both entities are weak entities

Identifying vs Non-Identifying Relationship
Identifying
Identifier of A is part of identifier of
Shown with solid line
ENROLMENT STUDENT Enrolment key includes student id, which is an identifier of student.
Non-identifying
Identifier of A is NOT part of
identifier of B. supports
Shown with broken line
Department no (identifier of department) is not part of Employees identifier.

Q4. The client indicates that a professor may teach several classes, but some professors do not have any assigned classes. Each class is taken by only one professor. Note that in this diagram, each class has a unique class id (class_id). Pick the most appropriate relationship for this business rule.

Types of Attributes
Cannot be subdivided
Age, sex, marital status
Composite
Can be subdivided into
additional attributes
Address into street, city, zip
Single-valued
Can have only a single
Person has one social security number
Multi-valued
Can have many values
Person may have several college degrees
Can be derived with
Age can be derived from date of birth
Attribute classification is driven by Client requirements
Phone Number?

Q5. The HiFlying case study indicates HiFlying establishes a drone hire rate as a cost per hour for customers to rent this particular drone (rates per hour are often changed over the life of the drone, as it ages, although they are only interested in recording the current cost per hour for the drone). Note that although the hire rate may change over the life of the drone, it is not directly related to the hours flown.
What type of attribute is the drone hire rate?
B. Composite
C. Single-valued D. Multi-valued
E. Derived

Multivalued Attribute
An attribute that has a list of values.
For example:
Car colour may consist of body colour, trim colour, bumper colour.
Crows foot notation does not support multivalued attributes. Values are listed as a separate attribute.

Resolving Multivalued Attributes
Note the Crows Foot model shown here has been modified from the text version

Associative (or Composite) Entity

Associative (or Composite) Entity
Q6. Show all attributes for the three entities and add KEYS:

Associative or Composite Entities

Q7. STEP 1: List ALL entities and their key attribute/s which exist in the case study.
For example:

HiFlying Drones Step 1 Identify Main Entities

Q8. STEP 2: Identify the relationships which exist between these entities (remember to add an appropriate verb):

Q9. Since a customer makes a rental, should the database designer include a relationship between RENTAL and CUSTOMER?
A. Yes, it is an important relationship to capture
B. No, it is redundant information
C. It depends on the clients requirements

HiFlying Drones Step 2 Identify Relationships

Q10. HiFlying Drones Step 3 Add Non-Key Attributes

HiFlying Drones Step 3 Add Non-Key Attributes Final Model

Conceptual Model (Monash Software)
You have completed
Step 1 identify entities and keys
of the modelling process for Monash Software
After the workshop please proceed and complete:
Step 2 Identify Relationships, and
Step 3 Add all non key attributes
A video will be provided showing the full process (available from Sunday 5pm).

CS: assignmentchef QQ: 1823890830 Email: [email protected]