BACS1053: Database Management Case Study - Design a Normalized Database - Management Assignment Help

Assignment Task:

Task:

1. Objectives
This assignment is to assess a student’s ability:
 Write business rules for a chosen organization’s environment.
 Implement the appropriate techniques and methodologies applicable to the database development life cycle.
 Critically analyse the requirements for a good database design and design a good database pertaining to the given scenario.
 Develop the designed database using Structure Query Language (SQL) in the ORACLE database server.
 Present the complete assignment report in a well organised, clear and concise manner.
2. Assessment
The learning outcomes assessed are:
CLO2: Demonstrate the appropriate Structured Query Language (SQL) statement to query and manipulate data from a database. (P4, PLO3)
CLO3: Design a normalized database system for a business scenario using relational database management software (CTPS, C4). Contribution of marks to coursework:
 The assignment deliverables* contributes 60% to the coursework component as shown below:

Tasks CLO Marks
Develop Business Rules 3 10
Develop Entity-Relationship Diagram (ERD) using Microsoft Access or any other diagramming tools

Develop the database schema – 3NF 3 10
Create database tables using ORACLE 2 20
Create records 2 10
Create queries 2 30

* Note: Tutor has the right to adjust the marks based on the % contribution by each team member.

Refer to the Assignment Rubrics for details.
3. Group Formation
Students will be assigned to groups of 3-4 members per team from the same tutorial group by the tutor. Every member in the group is expected to contribute and participate actively in the entire process of completing the assignment. Sharing of ideas and assistance in the completion of assignment among members is required. The students are expected to work in a team of 3 who are from the same tutorial group. Students will be individually assessed and therefore, every member in the group is expected to contribute and participate actively in the entire process of completing the assignment.

IMPORTANT: The award of marks will take into consideration the scope (i.e. complexity, correctness, clarity and completeness) of the tasks successfully completed by the student and deliver work on schedule.

4. Assignment Tasks
StaySweetHome Properties Sdn Bhd is a real estate brokerage company with many branches in a number of cities and deals with a range of properties, including residential houses and apartments and commercial properties. They provide services for buying, selling, rental and leasing of the properties. With the increasing number of services and business transactions, the higher management of StaySweetHome has decided to develop a Property Management System (PMS) to store and manage all the business records and data in a database environment. Assume that the PMS is accessible on the network by all the branches.

The basic requirements as below (you may add any feasible requirements to the database system):
 Maintain information about the company branches, staff information. Each staff is associated with one particular branch
 Maintains information about properties selling. Apart from the properties’ details, StaySweetHome also wants to record on the listing agents/realtors (the staff who managed the property).
 StaySweetHome deals with a number of different kinds of clients: buyers and renters. There are two types ofrental property owners: private owners and business owners.

 All the service transaction records will be recorded in the Database based on the type of transaction (selling or rental).

Task 1: Develop Business Rules (10 marks)
List down at least TEN (10) potential Business Rules that is relevant to the above scenario. You may add in business processes and assumptions that you deemed suitable.

Task 2: Develop Entity-Relationship Diagram (ERD) (10 marks)
Draw a conceptual Entity-Relationship Diagram (ERD) using the Crow’s Foot symbols for your whole system based on the above rules. A total of at least EIGHT (8) entities should be included.

Task 3: Develop the database schema – 3NF (10 marks)
Write out the database schema after normalizing the design to Third Normal Form (3NF) relations.
You should have suitable number of attributes for each of the entities.
Note: Underline all primary keys and use * to indicate foreign keys.

A sample format is shown below:
Customer (CustNo, CustName, CustAddress, CustContact)
Order (OrderNo, OrderDate, OrderAmount, CustNo*)
OrderDetails (OrderNo, ProductNo, Quantity, SellingPrice) Product (ProductNo, ProductDesc, UnitPrice)

Task 4: Create database tables in Oracle (20 marks)
Write the appropriate Structure Query Language (SQL) statements to create tables based on the entities identified above. Choose appropriate data types, default values and check constraints. Ensure that you enforce the Entity Integrity Rule and the Referential Integrity Rule.

Task 5: Create records (10 marks)
Write the appropriate INSERT statements to populate your database. Enter at least 10 records for each base (parent) table and at least 100 records for all other remaining tables. Website to generate random records: https://www.mockaroo.com/
For Task 4 & 5,
You are advised to insert sufficient data records for each table that you have created in the Oracle database server. Transaction tables would have more records than base tables.
For example, if there are 10 customers, average of 2 orders per customer per month with average of 2 items ordered per order.
Base Table
Customer table: 10 records
Transaction Table (at least 3 years)
Orders table: 10 * 2 * 12 = 240 records per year
OrderDetails table: 240 * 2 = 480 records per year

Furthermore, you should create sample data that has different date/month/year to simulate a real- world environment.

Task 6: Create Queries (30 marks)
 Produce queries to extract meaningful information for decision making. Single table queries are not allowed. Use a script to create your queries.

 Create FIVE (5) types (each member) of multi-table queries to extract useful meaningful information from the database.

For each of the 5 queries, state the purpose of the query and provide the results of the queries (screenshot). You should format the output of the queries to be meaningful and presentable. 

Make use of the ACCEPT- PROMPT and variable substitution to make your query as flexible as possible.
 Apply report formatting features.
 Explain the need and the importance of the queries and reports created for the company.
 Only SELECT statements.

Deliverables

 Idea of queries cannot be duplicated among same group members.

Task 7: Final Assignment Report (10 marks):
Compile the work done and prepare a well presented report for submission. Presentation in the lab
 Run a single script file from Task 4 and 5 to create the database.
 Run the scripts from Task 6.? Each group should take 30 minutes for the presentation and Q & A.
* All the above tasks handled by each student in the team must be clearly indicated.

This BACS1053: Management Assignment has been solved by our Management  Experts at My Uni Paper. Our Assignment Writing Experts are efficient to provide a fresh solution to this question. We are serving more than 10000+ Students in Australia, UK & US by helping them to score HD in their academics. Our Experts are well trained to follow all marking rubrics & referencing style.

Be it a used or new solution, the quality of the work submitted by our assignment experts remains unhampered. You may continue to expect the same or even better quality with the used and new assignment solution files respectively. There’s one thing to be noticed that you could choose one between the two and acquire an HD either way. You could choose a new assignment solution file to get yourself an exclusive, plagiarism (with free Turnitin file), expert quality assignment or order an old solution file that was considered worthy of the highest distinction.