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 PowCoder代写 加微信 powcoder
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 Crow’s 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 Crow’s 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 Employee’s 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.
▪ Crow’s foot notation does not support multivalued attributes. Values are listed as a separate attribute.
Resolving Multivalued Attributes
Note the Crow’s 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 client’s 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代考 加微信: powcoder QQ: 1823890830 Email: powcoder@163.com