Please answer the 3 Questions. Slide attached
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 22 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 inserting/updating/deleting data; Part 3 – Create & use databases with MySQL Community Server 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 recorded?) 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 nbr 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, nbr of concurrent 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 corresponds 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? * PartIDPartNameQtyInStock 1Kitchen table0 2Mixing bowl8 3Cutting board25 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! * PartIDPartNameQtyInStock 1Kitchen tableABC 2Mixing bowl8 3Cutting boardDf45..@#++ 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/real/single data type would permit this: You can’t have 3.24568 kitchen tables!! * PartIDPartNameQtyInStock 1Kitchen table3.245682 2Mixing bowl8 3Cutting board5.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. * PartIDPartNameQtyInStock 1Kitchen table-15 2Mixing bowl8 3Cutting board25 * 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 * PartIDPartNameQtyInStock 1Kitchen table0 2Mixing bowl 3Cutting board25 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. PartIDPartNameQtyInStock 1Kitchen table0 2Mixing bowl8 3Cutting board25 PartIDOrderIDQtyOrderedQtySent 1O04553 2O04510 1O04622 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 * PartIDOrderIDQtyOrderedQtySent 1O04553 2O04510 1O04622 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="" errors="" 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="" incorrect="" 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="" error="" 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 faster Indexes make insertion and deletion slower. All files can be indexed, regardless of file type Judicious choice of indexes can make your database run faster * Index - illustration * Big data file, slow to search… Smaller index file, Faster to search… PartIDPartNameWare house… 1Kitchen tableBrisbane 2Mixing bowlSydney 3Cutting boardBrisbane …Brisbane …Perth …… …… WarehousePhysical pointer 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 recorded?) 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