Reference no: EM132402965
ICT50115 Diploma of Information Technology, ICTDBS502 Design a Database
Australian Institute of Business and Management, Australia
Assessment Task 1 – Unit Knowledge Test (UKT)
Purpose of the assessment task:
The purpose of this assessment task is to
• explain the process of data analysis, particularly in determining data types and data structures, query and report design
• describe the data modelling related to developing the conceptual data model
• explain how data redundancy is identified
• describe database management system (DBMS) fundamentals, particularly during the design phase
• explain encryption and authentication as these apply to database security features
• outline the functions and features of data types, and data structures
• describe the functions, and features, of databases
• describe logical design concepts, particularly those related to designing data structures, queries screens and reports
• outline the object model design concepts, particularly those related to designing data structures, queries, screens and reports
• explain the term ‘scalability’ as it applies to databases.
Unit Knowledge Test (UKT)
Question 1: Explain the process of data analysis, particularly in determining data types and data structures, query and report design. Write your response in 100-150 words.
Question 2: Describe the data modelling related to developing the conceptual data model. Write your response in 100-200 words
Question 3: Explain how data redundancy is identified and minimised during database design . Write your response in 100-200 words.
Question 4: Describe database management system (DBMS) fundamentals, particularly during the design phase. Write your response in 100-150 words.
Question 5: Explain encryption and authentication as these apply to database security features. Write your response in 100-200 words
Question 6: Outline the functions and features of data types, and data structures. Write your response in the space provided below.
Question 7: Describe the functions, and features, of databases. Write your response in 100-200 words.
Question 8: Describe logical design concepts, particularly those related to designing data structures, queries screens and reports. Write your response in 100-200 words.
Question 9: Outline the object model design concepts, particularly those related to designing data structures, queries, screens and reports. Write your response in 100-200 words.
Question 10: Explain the term ‘scalability’ as it applies to databases. Write your response in 100-200 words.
Task 2: Determine the database requirement and Business Analysis
What to submit:
Write your report in a word document containing your documented findings. Please include the summarised business rules; the conceptual model; and your completed logical data model sign-off form, as they are specified in the below questions.
Business Analysis and Technical Functional Requirements
Document the functional requirements for Amazing Restaurant. If you are unsure or wish to clarify any requirements, consult with your trainer who will act as the client. Be sure to only document requirements that fall within the boundaries of the system to be developed.
Business Rules
From the details given in the scenario further above, create a summary of the business rules surrounding the software to be developed for Amazing Restaurant. If you are unsure or wish to clarify any business rules, consult with your trainer who will act as the client. Be sure to only document rules that fall within the boundaries of the system to be developed.
Conceptual Models
Draw a conceptual model of the database to power these functional requirements whilst still meeting the business rules. Identify just the entities and relationships between these entities in your conceptual model. For each relationship, indicate the maximum cardinality/participation in your diagram i.e. 1:m, 1:1, m:m. You may show minimum cardinalities/participation in your relationships, but it is not compulsory.
Conceptual Model Sign off
You will need to validate your conceptual data model with AR Management (your trainer will play the role of AR Management and provide you with feedback). Complete the below Conceptual Data Model sign-off form and submit it in your assessment document for signing and final approval (your trainer won’t actually sign this form but will return it to you with feedback and the YES/NO acceptance column completed).
If the feedback requires that you need to make changes then you take on-board their recommendations, make any changes necessary to resolve the issues, and resubmit your conceptual model again for final approval.
Task 3: Develop a logical data model and documenting database design
In this task, you will need to expand your conceptual model into a logical model where you will include attribute (columns)s for each entity along with their data types. Primary keys and keys for each entity.
Logical ERD
What to do and submit
Using Toad Data Modeler, create an ER model diagram (also known as EER) showing each entity from the conceptual model. Complete the data structures by including all the attributes (columns) for each entity (table) along with their data types. Primary key attributes must be shown.
For any column that is an auto-incrementing column, indicate it by ticking the property for that attribute (at this stage, do not worry about including indexes, not null constraints or any other constraints for any of the attributes).
Look to see if you have any m:m relationships in your conceptual model. If you do, you will need it break each one down into two 1:m relationships with a connecting (associative) entity in between them. If there are any new entities or entities to remove as you change from conceptual to logical, then go ahead and make the changes.
1) Update your ER model by going through every attribute (column) in every entity (table) to show whether the column:
a. is NOT NULL or whether nulls are allowed
b. values are UnSigned for numeric data types
2) MySQL will automatically create indexes on primary key attributes, foreign key attributes, and unique attributes. Update your ER model by creating indexes on any other attributes you believe justify an index.
3) Use Toad data modeller or any other software program to modify your conceptual model to create your logical model. Insert your completed conceptual and logical models as images into your word document that you will be submitting. You are not allowed to submit any hand drawn diagram
Document the Database Design
Document the tables that are derived from this ER diagram that you have created. Make sure that table names are in bold; Primary keys are underlined; Foreign keys are not explicitly labelled as foreign keys.
Normalisation
Use normalisation to validate that your ER design is valid. If you have ‘denormalised’ your design, explain the reasoning for your denormalising and what changes you made as a part of the denormalising.
Data Dictionary
Document the tables that are derived from this ER diagram that you have created. List all the attributes and their characteristics such as table name, attributes name, description, data type, data format, primary or foreign key and foreign key referenced table .
Create the Database
1) Using Toad data Modeler, generate an SQL script from your ER model of the database design. Put the generated SQL script into the Word doc as your answer to this problem.
2) Run this generated SQL script in a query window to physically create the database. Be careful if you receive errors on foreign key constraints that the foreign key and primary keys being connected are exactly the same data type and are identically signed
3) Check that both primary and foreign key are EXACTLY the same data type i.e. INT is not the same as SMALLINT etc
4) Check that both that both primary and foreign key are either signed or unsigned for numeric data types.
Having physically created the database, you can compare this back to your conceptual and logical designs and ensure it is correct making any changes necessary.
What to Submit
Generated SQL script and checks for the specific requirements. Include screenshots as evidence that data validation has been implemented. Save the scripts in the file SID_ARGenerated Scripts.docx
As evidence you have successfully created the database, please do the following:
o export the database tables and submit the sql file
o use Snipping Tool or some other technique to get a screenshot of the created tables in the Navigator Schema window of MySQL.
Data Validation
To ensure consistency of the data in the database you are required to develop data validation rules to ensure the correctness of data entered into the database table; identify referential Integrity constraint and database constraints. Below is a list of checks you should implement:
1. Staff gender should be recorded as ‘M’ or ‘F’.
2. Ensure that every customer has a telephone number which is unique
3. Staff numbers should be generated automatically using a sequence.
What to Submit
Show proof (codes of screenshot) of validation rules you have developed for data
Populate the database
What to do and submit
1) Populate some sample data into your database in order to perform queries. You will need to show screen shots of your populated database tables in your final report.
2) Ensure you have a suffice amount and diversity of sample data in the database to reflect the Amazing Restaurant scenario to answer the questions below.
Task 4: Design queries, screens and reports
User Interface requirement
This User Interface Requirement (ICTPRG527 Component)
Describe the requirements for designing a User interface (including screens and outputs) for Amazing Restaurant
SQL queries
What to do
Use the Amazing Restaurant database to design queries based on certain requirements.
Write SQL query statements to generate output reports for the following.
Single Table Queries
1. Display the list of unique cities where the customers lived in. Use ‘Customers Cities' as the output header.
2. Display the city and last name of customers who live in cities containing a particular string (such as 'syd'). This may vary based on the data in your table. Sort the results based on the customer's city in ascending order and then by the customer's last name in descending
order.
3. For employees who were hired between certain dates (please specify the range of dates based on your input data), display their first and last names and their hired dates. Order the results based on the hired date in descending order.
Multiple Table Queries
4. For all bookings with more than one booking, display the customer's number, last and first names and the number of bookings made by each customer.
5. For all dishes priced $30 or less, display the dish name, quantity ordered, dish price and the total price for each order. Ordered price is the total quantity ordered by multiply by the unit dish price.
What to Submit:
Run your SQL query solution/s from the previous question (1 to 5 above) and provide a screenshot of your query plus output. Provide both the SQL statement and a screenshot (you can use Snipping Tool to grab it) of the results of running your SQL statements.
Task 5: Design access and security system (ICTPRG507 component)
The database should only be accessed by authorised staff, who will be provided with a username and password, which serves as unique ID. Therefore, you will need to review the business security plan and design the password and access system for the database.
What to do
In this task, you will outline the security requirements for the database.
System Security Strategy
Provide the details of strategy to align the business requirements in regard to authorisation.
System Authorisation and Authentication
Provide detailed information and describe the procedures necessary to access the system. If applicable, include how to get a user ID and log on to the system, as well as the actions a user must take to change and/or reset a password.
User Access Considerations
Describe the different users and/or user groups and the restrictions placed on system accessibility or use for each. Please outline what kind of privileges each user will have.
Database protection against SQL injections
Plan a secure input and output handling to prevent code injections vulnerabilities that allow hackers to get to the data in the database.
Task 6: Confirm the database design
Database backup and restore procedures
In this task, you are required to outline the policy for database backup and restore procedures. Document instructions on how to backup and restore your MySQL Amazing Restaurant database or provide a link to a website that contains instructions on how to do backups and restores of a MySQL database.
Confirming final report (database/document) with the client
You will need to attain final approval from AR Management (your trainer will play the role of AR Management) for your physical data model. Complete the below Physical Data Model sign-off form and submit it in your assessment document for signing and final approval (your trainer won’t actually sign this form but will return it to you with feedback and the YES/NO acceptance column completed).
If the feedback requires that you need to make changes then you take on-board their recommendations, make any changes necessary to resolve the issues, and resubmit your work again for final approval.
Attachment:- Design a Database.rar