TECH1400 Database Design and Management Case Study Sample
IT - Case Study Analysis and Design
Your Task
This assessment is to be completed individually. In this assessment, you will design an Entity Relationship Model (ERM) for a real-world case study of an organisation that uses a relational database, focusing on the key concepts of database relationships and normalisation.
Assessment Description
This assessment requires you to design an Entity Relationship Model (ERM) for a real-world case study of an organisation that uses a relational database as part of their operations. You will need to focus on the key concepts of database relationships and normalisation covered in the first four weeks of the course. You will need to demonstrate your ability to apply these concepts to a real-world scenario and create an ERM that accurately represents the data relationships and structures of the chosen organization's database.
This assessment aims to achieve the following subject learning outcomes:
LO1 Explain Relational Database Management Systems and their application within organisations.
Assessment Instructions
• You are required to complete the following tasks:
1. Choose an organisation that uses a relational database as part of their operations. This could be a business, government agency, non-profit organization, or other type of entity.
2. Research the organisation and their use of a relational database. Identify the types of data that are stored in the database, the relationships between the different data sets, and any key terminology or concepts that are relevant to the organisation's use of the database.
3. Design an ERM for the organisation's database, using appropriate notation and symbols to represent the data relationships and structures. Your ERM should demonstrate your understanding of the key concepts of database relationships and normalisation covered in the first four weeks of the course.
4. Write a report that explains the design choices you made in creating the ERM and how they relate to the key concepts of database relationships and normalisation. Your report should be submitted in Word Document or PDF format and include the following sections:
• Introduction: Provide a brief overview of the chosen organisation and their use of a relational database.
• Entity Relationship Model Design: Present your ERM design, using appropriate notation and symbols to represent the data relationships and structures. Explain the design choices you made and how they relate to the key concepts of database relationships and normalisation.
• Application in the Chosen Organisation: Analyse the organisation's use of a relational database, including the types of data stored, the relationships between data sets, and any key terminology or concepts that are relevant to their use of the database. Discuss how your ERM design reflects and supports the organisation's use of a relational database.
• Conclusion: Summarise the key points of your report and provide recommendations for how the organization could improve their use of a relational database. A summary of your design decisions and any limitations of your database design.
• The report should be no more than 1500 words and should include at least 8 academic sources to support your arguments. You must use KBS referencing and citation to acknowledge the sources of your information.
• Please refer to the assessment marking guide to assist you in completing all the assessment criteria.
Solution
Introduction
Database design and management is a very crucial process. This study focuses on the relational database management system and its application for the Oracle Corporation which is a product, service, and assistance-providing firm related to the cloud and database technology. It also deals with numerous software and hardware related to the database management system.
Oracle
Oracle employs a relational database to effectively handle and analyze its massive volumes of data, yielding insightful insights and allowing for well-informed business choices, as will become clear to the reader by the conclusion of this study. University assignment help, Database management systems are only one of the many products and services offered by Oracle Corporation, a technology firm. The Oracle Database is the company's most well-known offering; it is a relational database management system (RDBMS) utilized by a broad variety of businesses and governments throughout the globe (Oracle.com, 2023). Oracle Database is a reliable and scalable database platform utilized by many large companies for their most important programs. It's a full-featured DBMS that can deal with intricate data structures and is renowned for its speed and dependability. Data in Oracle Database is structured and managed according to a relational paradigm. It maintains information in tables linked using keys (Bhat, Bhirud and Bhokare, 2022). This facilitates quick data retrieval after a query. Oracle is a favorite among businesses that need reliable and secure access to their data because of its backup and recovery capabilities and security measures. Oracle also provides various database-related products and services, such as the self-driving Oracle Autonomous Database platform, the open-source relational database Oracle MySQL, and the distributed key-value database Oracle NoSQL Database. These systems provide a wide variety of workloads and use cases, giving businesses a wide range of options when selecting a database.
Entity Relationship Model Design
Figure 1 ERM
(Source: Author)
(Source: Author)
The entities in this ER diagram are ? Customers, Orders, Products, and Suppliers. Relationships between these entities include "Order placed by Customer," "Product included in Order," and "Supplier provided Product."
Attributes
.png)
Relationships
.png)
.png)
Application in the Oracle’s relational Database
The Oracle Database is a product of Oracle Corporation, a technology firm that specializes in database management systems. Information about customers, finances, products, and more are all kept in a relational database that Oracle utilizes to run its company.
Oracle's relational database stores a variety of important data, including:
- Oracle keeps client information such as names, addresses, phone numbers, email addresses, and shopping habits. This information is used for targeted advertising and to improve the quality of the products and services offered to consumers.
- Oracle keeps information on its workers, including their names, addresses, job titles, salaries, and performance evaluations. Payroll and employee assessments would be impossible without this information.
- Oracle maintains data like product descriptions, prices, and stock levels in its product database. This information is vital for supply chain management and for giving consumers relevant details about products.
- Oracle also keeps track of money-related information including sales, costs, and profits. This information is utilized to keep tabs on the company's finances and make smart business choices.
Keys, such as primary keys and foreign keys, are used to maintain the associations between these databases. For instance, a foreign key might be used to associate a customer's purchase history with the things they bought. Relational database ideas and terms like tables, fields, records, and queries are also used in Oracle (Oracle.com, 2023). Each column in a table may be used to store a different sort of information, such as a customer's name or the price of an item. For example, if a person wants to find all of the customers who purchased in the previous month, he/she may use a query to search for that information in the company’s database.
1. Tables ? Data may be organized into groups of relevant information by using tables. There are rows and columns in each table, and they each have their names.
SQL (Structured Query Language) instructions are used to build tables in Oracle, and each table needs a primary key to ensure that each entry in the table can be uniquely identified.
2. Records ? These are the groups of fields that make up individual pieces of information. Fields for a client's name, address, phone number, and email address could be included in a customer table record (Peterson, 2020). The main key identifies and distinguishes each record.
3. Fields ? It is often called columns, which are the discrete pieces of information included in a table. Fields are used to store and organize discrete pieces of information, such as names, and prices. Each field has its own name and data type, and the information it contains may be required or elective.
4. Queries ? query is a request for data from a database that meets a set of predefined criteria. Using a query, one may get a list of all customers who have bought anything in the previous month. SQL, PL/SQL, and SQL*Plus are only some of the query languages that Oracle is compatible with (Islam et al., 2017).
The ERM of Oracle for the database has several features that reflect and facilitate the company's usage of a relational database (Ilic et al., 2021). The rows and columns of a table are used to organize related data for easy access and modification.
.png)
.png)
Conclusions
Oracle's usage of a relational database was covered in this study, along with the many data types stored there, the connections between them, and the important ideas and terms involved. Based on the data provided, an Entity Relationship Diagram was demonstrated for Oracle's RDBMS. According to the findings of this study, Oracle should keep table-wide data normalization and consistency as a top priority. Oracle should take serious data security precautions to safeguard the private information kept in its database. Some examples of these kinds of safeguards include password requirements, restricted access, and encrypted data. To further acquire insights from their data, the major recommendation is that Oracle investigates data analytics and visualization technologies. The company may be better able to see patterns in its data and use that knowledge to guide its business choices as a result.
References
Bhat, S., Bhirud, R. and Bhokare, V., 2022. Small-Scale Relational Database Management System. In 2022 IEEE Pune Section International Conference (PuneCon), pp. 1-7.
Ilic, M., Kopanja, L., Zlatkovic, D., Trajkovic, M. and Curguz, D., 2021. Microsoft sql server and oracle: Comparative performance analysis. In The 7th International conference Knowledge management and informatics, pp. 33-40.
Islam, K., Ahsan, K., Bari, S.A.K., Saeed, M. and Ali, S.A., 2017. Huge and Real-Time Database Systems: A Comparative Study and Review for SQL Server 2016, Oracle 12c & MySQL 5.7 for Personal Computer. Journal of Basic & Applied Sciences, 13, pp.481-490.
Oracle.com. (2023). Database Design Techniques. [online] Available at: https://docs.oracle.com/cd/A87860_01/doc/paraserv.817/a76970/design.htm [Accessed 17 Apr. 2023].
Peterson, R. (2020). Database Design in DBMS Tutorial: Learn Data Modeling. [online] Available at: https://www.guru99.com/database-design.html [Accessed 17 Apr. 2023].
S, R.A. (2021). What is Normalization in SQL? 1NF, 2NF, 3NF and BCNF in DBMS. [online] Simplilearn.com. Available at: https://www.simplilearn.com/tutorials/sql-tutorial/what-is-normalization-in-sql [Accessed 17 Apr. 2023].
Would you like to schedule a callback?
Send us a message and we will get back to you
Highlights
Earn While You Learn With Us
Confidentiality Agreement
Money Back Guarantee
Live Expert Sessions
550+ Ph.D Experts
21 Step Quality Check
100% Quality
24*7 Live Help
On Time Delivery
Plagiarism-Free
81 Isla Avenue Glenroy, Mel, VIC, 3046 AU
