Question 1 2 Marks This question refers to the Customer table as used in this unit lecture slides about the customers placing orders in the homeware database. Define a view to display the details of...

Question 1     2 Marks
This question refers to the Customer table as used in this unit lecture slides about the customers placing orders in the homeware database. Define a view to display the details of customer(s) from Sydney. In addition, using SQL DCL command to write a statement to permit a user with the log-in ID of DPearson to access this view and update its Street value. Refer ppt slide herewith
Question 2    2 Marks
In a DBMS, triggers can be used to track the changes in table data. Briefly describe the steps (by using triggers) that keep tracking the changes of the Customer table for the homeware database which is used in this unit as a sample database. You are also required to define a trigger that you can track the changes of the customer table when some records are deleted. Refer ppt slide herewith
Question 3     2 Marks
Consider a relation named STUDENT_ ACCOMMODATION (StudentID, Buidling, AccommodationFee) as shown in the figure below. Explain why this relation is in 2NF but not in 3NF.
    StudentID
    Building
    AccommadtionFee
    101
    Alpha
    $320
    102
    Betta
    $250
    103
    Alpha
    $320
    104
    Betta
    $250
    105
    Gemma
    $400

Lecture 1
COIT20247 Database Design and Development
Week 6 Create and Use databases with MySQL
Objectives
    This week lecture slides consist of three parts:
Part 1 – Key aspects of database physical design
Part 2 - SQL overview, creating data structures and
XXXXXXXXXXinserting/updating/deleting data;
Part 3 – Create & use databases with MySQL
XXXXXXXXXXCommunity Serve
    Describe what is involved in physical design and where it fits in the overall database design process
    Describe what is meant by index, including its advantages and disadvantages
Objectives (continued)
    Describe SQL data definition language (DDL)
    Use SQL DDL to create tables and insert data
    Describe how to create/use MySQL databases
    Describe to export a MySQL database by using Workbench
*
Part 1: Key aspects of database physical design
    So far we have done:
    Conceptual modelling
    Logical design:
    Transform ER model to relations
    Check relations for normalisation
    Next: physical design
    Purpose of physical design: translate logical design into specifications for physical storage
*
What is physical design?
There are two goals in physical design:
Data integrity
Efficiency
These two goals often compete
We focus on achieving integrity & processing efficiency, not too concerned about storage efficiency
Physical design does not include the actual implementation of the database
*
Database analysis, design & development process
*
Examine existing processes
(what data needs to be
ecorded?)
Develop
conceptual
model
Implement
database
Check DB design
for normalisation
Do physical
design
Convert model
to logical
design
Develop application
or web-based interface
(will involve SQL)
Test and
maintain...
*
Physical design process
    Before starting, you need to know:
    Normalised relations, estimates of n
of rows
    Attribute definitions, plus physical specifications such as maximum length
    Where and when data are used
    Requirements for response times, security, backup, integrity etc
    Which DBMS (such as MySQL Server), data access language, etc is being used
*
Decisions you need to make
    Data type for each attribute
    Grouping attributes from logical design into physical records
    What file organisations will be used
    What indexes to create
    How to optimise queries to take advantage of indexes, file organisations etc.
*
Data volume & usage analysis
    Selection of file types, indexes etc should be guided by predicted usage volume and patterns
    Need estimates of timing of events, transaction volumes, n
of concu
ent users, reporting & querying activities
    Will there be any peak usage times?
    eg if usage patterns suggest that Enrolment and Course entities are often used at the same time, you may wish to combine these into one file
    If you know that most Student record retrievals will occur by primary key look-up, then you can index the primary key.
*
Designing fields
    A field co
esponds to a simple attribute
    For each field, you must choose:
    Data type and size
    What integrity controls to implement
    How to handle missing values
    Other issues, such as display format (not covered here)
*
Designing fields – choosing a data type
    Available data types depends on the DBMS
    Data types should be chosen to:
    Allow all legal values to be entered
    Improve data integrity
    Support data manipulations
    Minimise storage space
    If the field will be extremely large, you may need to consider coding or compression techniques
    
*
Designing fields – an example
    Consider the QtyInStock field in this table:
    What data type choices are appropriate here?
*
    PartID    PartName    QtyInStock
    1    Kitchen table    0
    2    Mixing bowl    8
    3    Cutting board    25
Designing fields – an example
    Only numbers make sense for QtyInStock, so it should be a number, not a text field.
    If you were to define QtyInstock as a text field, then entries like this would be possible:
    These values are meaningless and should be prevented!
*
    PartID    PartName    QtyInStock
    1    Kitchen table    ABC
    2    Mixing bowl    8
    3    Cutting board    Df45..@#++
Designing fields – an example
    For this particular stock table, only whole numbers make sense, so it needs to be an integer, not a float.
    A float
eal/single data type would permit this:
    You can’t have XXXXXXXXXXkitchen tables!!
*
    PartID    PartName    QtyInStock
    1    Kitchen table     XXXXXXXXXX
    2    Mixing bowl    8
    3    Cutting board    5.5
Designing fields – an example
    In many cases, you can’t have a negative quantity in stock:
    Therefore you should add a validation rule QtyInStock >= 0 *
    Note: some stock-keeping practices do allow negative quantities, you would need to check.
*
    PartID    PartName    QtyInStock
    1    Kitchen table    -15
    2    Mixing bowl    8
    3    Cutting board    25
* This is called a column validation rule. In Access, you can view or modify a column validation rule in the properties of any column. One of the interesting things to note about a column validation rule is that it can only refer to itself. That is, you cannot add a column validation rule for QtyInStock that refers to PartID. Any validation rule that refers to more than one column in a table (e.g. ColumnA > ColumnB) must be done in a different way – more later.
Designing fields – an example
    Does it make sense to allow QtyInStock to be null?
    We have 0 kitchen tables in stock, but how many mixing bowls? (Null is not the same thing as 0!)
    In most cases, a field like this should be defined as not null
*
    PartID    PartName    QtyInStock
    1    Kitchen table    0
    2    Mixing bowl
    3    Cutting board    25
Designing fields – an example
    However, the data type should not be excessively large, as this will waste storage space.
    The data type should support data manipulations
    Note that if QtyInStock were a text field, it would be difficult/impossible to add them all up.
*
Designing a field – an example
Add referential integrity to this relationship to ensure only valid parts are ordered
*
If part names are supposed to be unique, then add a unique index (more on this later) here to prevent duplicate entries.
    PartID    PartName    QtyInStock
    1    Kitchen table    0
    2    Mixing bowl    8
    3    Cutting board    25
    PartID    OrderID    QtyOrdered    QtySent
    1    O045    5    3
    2    O045    1    0
    1    O046    2    2
Designing a field – an example
    You can use table validation rules to ensure values in one column are greater than, less than etc another column.
    For example, consider this table from the previous slide:
    QtySent should probably never be greater than QtyOrdered
*
    PartID    OrderID    QtyOrdered    QtySent
    1    O045    5    3
    2    O045    1    0
    1    O046    2    2
Designing a field – an example
    So you need to add a validation rule like this:
[QtySent] <= [QtyOrdered]
(Note: table data validation rules, see Week 9 lecture slides)
    Which will prevent e
ors like this:
*
    PartID    OrderID    QtyOrdered    QtySent
    1    O045    5    15
    2    O045    1    5
    1    O046    2    2
* This sort of rule is called a table validation rule. If you need to refer to two different columns within your validation rule, then it needs to be done as a table validation rule and not a column validation rule.
Data integrity
    Data integrity is extremely important – it helps prevent the entry of inco
ect data
    Data integrity can be achieved by:
    Appropriate data type and size (see previous slides)
    Specifying a default value: reduces data entry time and reduces potential entry e
o
    Range control: limits the set of allowable values. For eg, if Quantity must be a positive value, you can specify that Qty > 0
*
Data integrity
    Data integrity can be achieved by:
    Null control: Some fields should never be null, you can specify that a value is required
    Referential integrity: You can (and almost always should) specify that a foreign key must either match a primary key value or be null
    Unique indexes: If a field should be unique, you can enforce this with a unique index (more later)
    Other integrity rules: Most DBMS allow you to create rules. For eg, assume that Qty must be a multiple of 5, you can specify that Qty MOD 5 = 0
    Triggers and procedures: covered in Week9
*
Indexes
    Indexes to a relation are like a table of contents to a book
    Indexes make data retrieval and updating faste
    Indexes make insertion and deletion slower.
    All files can be indexed, regardless of file type
    Judicious choice of indexes can make your database run faste
*
Index - illustration
*
Big data file, slow to search…
Smaller index file,
Faster to search…
    PartID    PartName    Ware house    …
    1    Kitchen table    Brisbane
    2    Mixing bowl    Sydney
    3    Cutting board    Brisbane
    …    Brisbane
    …    Perth
    …    …
    …    …
    Warehouse    Physical pointe
    Brisbane
    Perth
    Sydney
Rules of thumb for choosing indexes
    Useful on larger tables
    Specify a unique index for each primary key
    Useful for columns that frequently appear in a WHERE clause*
    Useful for columns that frequently appear in an ORDER BY clause*
    Useful when there is significant variety in the values of an attribute
    Select carefully if your DBMS limits size or number of indexes
    Some DBMSs do not index NULL values; if so then an index may be less useful if the attribute will have many missing values.
*
* Note: WHERE and ORDER BY clauses are part of SQL, which you will study shortly.
Unique vs non-unique indexes
    A unique index will prevent duplicate values in the column being indexed,
    e.g. assume part names are unique
    A unique index would prevent the same part name from being entered twice.
    A non-unique index permits duplicate values in the column being indexed
    Note: Unique indexes are a handy integrity tool!
*
The story so far…
    So far we have done:
    Conceptual modelling
    Logical design:
    Transform ER model to relations
    Check relations for normalisation
    Physical design (not including implementation)
    Next would be:
    Physical implementation
    Application development
*
Next would be…
    We won’t be doing a full system/application implementation (that’s another unit in its own right… this is a database unit)
    We’ll be looking at SQL to physically create/implement the database tables
    We’ll also be looking SQL as it would be used in application development & regular database use.
*
Database analysis, design & development process
*
Examine existing processes
(what data needs to be
ecorded?)
Develop
conceptual
model
Implement
database
Check DB design
for normalisation
Do physical
design
Convert model
to logical
design
Develop application
or web-based interface
(will involve SQL)
Test and
maintain...
*
Part 2: SQL overview