Download database design M Pass and more Schemes and Mind Maps Database Management Systems (DBMS) in PDF only on Docsity!
ASSIGNMENT 1 FRONT SHEET
Qualification TEC Level 5 HND Diploma in Computing Unit number and title Unit 04: Database Design & Development Submission date 10/10/2023 Date Received 1st submission Re-submission Date Date Received 2nd submission Student Name TRAN QUOC ANH Student ID BH Class SE206.06 Assessor name DAO TRAN CHUNG Student declaration I certify that the assignment submission is entirely my own work and I fully understand the consequences of plagiarism. I understand that making a false declaration is a form of malpractice. Student’s signature QUANH Grading grid
P1 M1 D
❒ Summative Feedback: ❒ Resubmission Feedback:
Grade: Assessor Signature: Date: Signature & Date:
Figure 8: Network Database..................................................................................................................................................... 11 Figure 9: RDSMSs ..................................................................................................................................................................... 12 Figure 10: Oracle Features ....................................................................................................................................................... 14 Figure 11: PostgreSQL .............................................................................................................................................................. 16 Figure 12: ER diagram .............................................................................................................................................................. 27 Figure 13:FPT shop database management ............................................................................................................................. 34 Figure 14:Shop table ................................................................................................................................................................ 34 Figure 15:CATEGORY table ....................................................................................................................................................... 35 Figure 16:SUPPLIER table ......................................................................................................................................................... 35 Figure 17: PRODUCT table........................................................................................................................................................ 35 Figure 18: STAFF table .............................................................................................................................................................. 36 Figure 19: CUSTOMER table ..................................................................................................................................................... 36 Figure 20: FEEDBACK table ....................................................................................................................................................... 36 Figure 21: ORDERS table .......................................................................................................................................................... 37 Figure 22: DETAIL ORDER table ................................................................................................................................................ 37 Figure 23:FPT shop database management diagram ............................................................................................................... 38 Figure 24:FPT Shop Manage Database..................................................................................................................................... 39
I. INTRODUCTION
In a small business, network administrators or developers also serve as database administrators (DBAs). In bigger companies, there may be hundreds of DBAs with distinct areas of expertise, such as design and architecture, maintenance, development, etc. No matter what area of IT you work in, you will eventually need to store data, thus it doesn't hurt for most people to have a basic understanding of databases and how they operate. This tutorial's objective is to give this fundamental introduction. We'll cover the fundamentals of what a database is and how it works, as well as its history, understanding relational databases, basic concepts like columns and rows, other types of databases, additional concepts to be aware of, and a quick review of the most important commercial systems available today. [1]
For the most part there are no prerequisites to this tutorial other than basic computing knowledge. Figure 1 : database
a) Centralized Databased: Figure 3 : Types of Database
Advantages of centralized Database:
- It has decreased the risk of data management, i.e., manipulation of data will not affect the core data.
- Data consistency is maintained as it manages data in a central repository.
- It provides better data quality, which enables organizations to establish data standards.
- It is less costly because fewer vendors are required to handle the data sets. Disadvantages of centralized Database:
- The size of the centralized database is large, which increases the response time for fetching the data.
- It is not easy to update such an extensive database system.
- If any server failure occurs, entire data will be lost, which could be a huge loss. b) Distributed Database Data is dispersed among several database systems within an organization, as opposed to a centralized database system, in distributed systems. Communications connections connect these database systems. These links make it easier for consumers to obtain the data. We can further divide a distributed database system into It is the type of database that stores data at a centralized database system. It comforts the users to access the stored data from different locations through several applications. These applications contain the authentication process to let users access data securely. An example of a Centralized database can be the Central Library that carries a central database ofeach library in a college/university.
c) Relational Database: This database is built on a relational data architecture, which stores data as rows (also known as tuples) and columns (also known as attributes), which together make up a table (also known as a relation). SQL is used by relational databases to store, manipulate, and preserve data. In 1970, E.F. Codd created the database. Every table in the database has a key that distinguishes the data from other tables. Properties of Relational Database: There are following four commonly known properties of a relational model known as ACID properties, where:
- A means Atomicity: This guarantees that the data operation will end successfully or unsuccessfully. The 'all or nothing' approach is used. As an illustration, a transaction will either be committed or canceled.
- C means Consistency: The value of the data should be retained before and after any action is performed on it. For instance, the account balance should be accurate both before and after the transaction, meaning it should stay saved.
- I means Isolation: Multiple users may simultaneously access the same data from the database. As a result, the data should continue to be isolated. One transaction consequence, for instance, shouldn't be apparent to the effects of other transactions in the database when numerous transactions happen at once.
- D means Durability: It ensures that once it completes the operation and commits the data, data changes should remain permanent. Figure 5 : Relational Database
Figure 6 : Cloud Database d) NoSQL Database: SQL-Free/Not Only A variety of different data sets can be stored in a SQL database type. It is not a relational database since it stores data in a variety of formats in addition to tabular form. It was created as the need for creating contemporary applications grew. In order to meet the expectations, NoSQL offered a wide range of database technologies. The following four types of NoSQL databases can be further separated.
- Key-value storage: It is the simplest type of database storage where it stores every single item as a key (or attribute name) holding its value, together.
- Document-oriented Database: A kind of database that uses documents similar to JSON to store data. By employing the same document-model format as utilized in the application code, it aids developers in saving data.
- Graph Databases: It is used for storing vast amounts of data in a graph-like structure. Most commonly, social networking websites use the graph database.
- Wide-column stores: It is similar to the data represented in relational databases. Here, data is stored in large columns together, instead of stored in rows. e) Cloud Database: A type of database where data is stored in a virtual environment and executes over the cloud computing platform. It provides users with various cloud computing services (SaaS, PaaS, IaaS, etc.) for accessing the database. There are numerous cloud platforms, but the best options are:
- Amazon Web Services (AWS)
- Microsoft Azure
- Kamatera
- PhonixNAP
- ScienceSoft
- Google Cloud SQL, etc f) Hierarchical Databases: It is the type of database that stores data in the form of parent-children relationship nodes. Here, it organizes data in a tree-like structure.
g) Network Databases: Figure 8 : Network Database The network data model is often followed by the database. Data is represented in this instance as a network of nodes that are linked together. It permits each record to have several children and parent nodes to construct a generalized graph structure, in contrast to a hierarchical database.
B. RDBMSs
1. What is the RDBMSs
Stands for "Relational Database Management System." An RDBMS is a DBMS designed specifically for relational databases. Therefore, RDBMSes are a subset of DBMSes. A database that uses rows and columns to store data in an organized manner is referred to as a relational database. As a result, it is simple to find and retrieve particular values inside the database. It is referred to as "relational" because the values in each table are connected to one another. Additionally, tables may be connected to other tables. Queries may be executed simultaneously across several tables because to the relational nature.[5]
Figure 9 : RDSMSs RDBMS refers to the database program itself, whereas a relational database refers to the kind of database that RDBMS handles. The program is responsible for doing queries on the data, including those that add, update, and look for values. The data may be represented visually by an RDBMS as well. For instance, it may provide data in tables, much like a spreadsheet, allowing you to see and even modify specific numbers. You can design forms in several RDBMS applications that will make it easier to input, amend, and delete data. Most well known DBMS applications fall into the RDBMS category. Examples include Oracle Database, MySQL, Microsoft SQL Server, and IBM DB2. Some of these programs support non-relational databases, but they are primarily used for relational database management. Examples of non-relational databases include Apache HBase, IBM Domino, and Oracle NoSQL Database. These types of databases are managed by other DBMS programs that support NoSQL, which do not fall into the RDBMS category.[6]
2. Popular RDBMSs
a) MySQL The most popular free open source database management system in the world, MySQL, is widely used in the creation of applications. Because MySQL is a quick, dependable, and simple-to-use database management system, it can run on many different operating systems and offer a wide range of powerful utility features. MySQL is appropriate for applications that access databases on the Internet because of its great speed and security. From the webpage, users may free download MySQL.. MySQL has many versions for different operating systems:
b) Oracle Relational database management systems include Oracle database. Additionally, it goes under the names Oracle DB and Oracle. Oracle Corporation is in charge of its production and promotion. It was developed in 1977 by engineers Lawrence Ellison and others. It is one of the most widely used relational database engines for storing, organizing, and retrieving data on the IT market.[11] The first DB created specifically for data warehousing and corporate grid computing was Oracle. The most adaptable and economical method of managing data and applications is provided by enterprise grid computing. It communicates with the database using SQL queries as the language. [4] Features of Oracle: Oracle database manages data with the help of an open, complete, and integrated approach. The following are features that complete the demand for powerful database management: Availability: It never goes offline or out of commission, ensuring the database's availability around-the-clock. Because of the Oracle Data Guard capabilities, it offers high availability of databases. Due to its feature, the secondary database can serve as a backup for the primary database in the event of a breakdown. As a consequence, none of the regular operations, including backups and partial failures, stop the database from being utilized. [12] Security: Oracle has a system in place for managing and accessing the database that guards against unauthorized use. Because of the Oracle Advanced Security features, it offers great security. It provides TDE (Transparent Data Encryption) and data redaction as two options for safeguarding databases. Data encryption is supported by TDE both at the source and after export. Application-level redaction takes place. Other security features offered by Oracle include Oracle Label Security and Oracle Database Vault, which controls user privileges. Figure 10 : Oracle Features
Scalability: It provides features like RAC (Real Application Cluster) and Portability, which makes an Oracle database scalable based on usage. In a clustered environment, it includes capabilities such as rolling instance migrations, performing upgrades, maintaining application continuity, quality of service management, etc. Performance: The performance improvement technologies offered by Oracle include Oracle Advanced Compression, Oracle Database In-Memory, Oracle Real Application Testing, and Oracle TimesTen Application-Tier Database Cache. Their major goal is to achieve the best degree of system performance. Analytics: Oracle has the following solutions in the field of analytics:
- OLAP (Oracle Analytic Processing): It is an implementation of Oracle for doing complicated analytical calculations on business data.
- Oracle Advanced Analytics: It is a technical combination of Oracle R Enterprise and Oracle Data Mining that assists customers in determining predictive business models through data and text mining, as well as statistical data computation. Management: Oracle Multitenant is a database management tool that combines a single container database with many pluggable databases in a consolidated design. Advantages of Oracle:
- Performance: Oracle has procedures and principles that help us to get high levels of database performance.
- Portability: The Oracle database can be ported on all different platforms than any of its competitors.
- Backup and Recovery: It is always better to take a proper backup of your entire oracle online backup and recovery. The Oracle database makes it easy to accomplish recovery quickly by using the.
- PL/SQL: One of the greatest benefits of using the Oracle database is to support PL/SQL extension for procedural programming. Disadvantages of Oracle:
- Complexity: When people lack the necessary technical knowledge and expertise to work with the Oracle Database, Oracle is not advised for usage. Additionally, it is not suggested to utilize it if the business wants a simple to use database with restricted capability.
- Cost: Compared to competing databases, the cost of Oracle products is relatively expensive. Users are therefore more inclined to use alternative, less expensive choices like MS SQL Server, MySQL, etc.
- Difficult to manage: Oracle databases are often much more complex and difficult in terms of the management of certain activities. c) PostgreSQL: A top-notch open source database management system is PostgreSQL. For flexibility and SQL compliance, it allows relational and non-relational queries in both SQL and JSON. Advanced data types and performance- enhancing tools are only supported by pricey commercial databases like Oracle and SQL Server; however, PostgreSQL has these features built in. It also goes by the name Postgres.
- Many open source apps support MySQL, but may not support PostgreSQL.
- On performance metrics, it is slower than MySQL. d) Microsoft SQL Server: A relational database management system (RDBMS) called Microsoft SQL Server serves a wide range of business intelligence, analytics, and transaction processing applications in corporate IT settings. Along with Oracle Database and IBM's DB2, Microsoft SQL Server is one of the three database technologies that dominate the industry. SQL is a standardized programming language that database administrators (DBAs) and other IT professionals use to administer databases and query the data they contain. Microsoft SQL Server, like other RDBMS software, is built on top of SQL. Transact-SQL (T-SQL), a Microsoft implementation of SQL that enhances the basic language with a number of proprietary programming extensions, is a component of SQL Server.[13] Features of Microsoft SQL Server:
- Intelligent Query Processing Enhancements.
- Accelerated Database Recovery (ADR).
- Always Encrypted with secure enclaves.
- Memory-optimized Tempdb metadata.
- Query Store custom capture policies. Advantages of Microsoft SQL Server:
- Increases data security: One of the primary purposes of Microsoft SQL Server is ensuring the security of your database, especially with a Microsoft SQL Server database administration service.
- Ease of configuration: Unlike other database management software, the installation and configuration of Microsoft SQL Server are easier.
- Optimized data storage: This allows you to manage data easily and efficiently with minimal troubleshooting and maintenance.
- Data recovery support: In the case of power interruption or server shutdowns, data may become corrupted. Microsoft SQL Server eliminates the risk of losing data by having features for data recovery and restoration. Disadvantages of Microsoft SQL Server:
- Cost: There is a free version of Microsoft SQL Server that you can use. But for more advanced database applications and features, you will have to make a sizable investment for higher versions of the software.
- Restricted compatibility: Making additional investments in Microsoft software may be needed if your business uses little to no Microsoft infrastructure. These extra commitments might also cost your company more, but they will allow you to use Microsoft SQL Server on your platform.
- Hardware restrictions: Newer versions of Microsoft SQL Server need advanced technologies to run. So, if your hardware consists mainly of older hardware, you might need to invest in newer machines in order to use Microsoft SQL Server. Moreover, if your business needs a larger database, you also need to increase your hard drive space.
e) MongoDB: MongoDB is a document-oriented NoSQL database used for high volume data storage. Instead of using tables and rows as in the traditional relational databases, MongoDB makes use of collections and documents. Documents consist of key-value pairs which are the basic unit of data in MongoDB. Collections contain sets of documents and functions which is the equivalent of relational database tables. MongoDB is a database which came into light around the mid-2000s. Features of MongoDB:
- Each database contains collections which in turn contains documents. Each document can be different with a varying number of fields. The size and content of each document can be different from each other.
- The document structure is more in line with how developers construct their classes and objects in their respective programming languages. Developers will often say that their classes are not rows and columns but have a clear structure with key-value pairs.
- The rows (or documents as called in MongoDB) don't need to have a schema defined beforehand. Instead, the fields can be created on the fly.
- The data model available within MongoDB allows you to represent hierarchical relationships, to store arrays, and other more complex structures more easily
- Scalability – The MongoDB environments are very scalable. Companies across the world have defined clusters with some of them running 100+ nodes with around millions of documents within the database. Advantages of MongoDB:
- It is very easy to install and set up MongoDB.
- The very basic feature of MongoDB is that it is a schema-less database. No schema migrations anymore. Since MongoDB is schema-free, your code defines your schema.
- The ability to derive a document-based data model is one of the most attractive advantages of MongoDB. Because, the way it stores the data in the form of BSON (Binary JSON), ruby hashes etc, helps to store the data in a very rich way while being capable of holding arrays and other documents.
- The document query language supported by MongoDB plays a vital role in supporting dynamic queries.
- Very easy to scale.
- Due to the structuring (BSON format - key value pair) way of the data in MongoDB, no complex joins are needed. Disadvantages of MongoDB:
- Joins not Supported: MongoDB doesn’t support joins like a relational database. Yet one can use joins functionality by adding by coding it manually. But it may slow execution and affect performance.
- High Memory Usage: MongoDB stores key names for each value pair. Also, due to no functionality of joins, there is data redundancy. This results in increasing unnecessary usage of memory.
- Limited Data Size: You can have a document size, not more than 16MB.
- Limited Nesting: You cannot perform the nesting of documents for more than 100 levels. f) Redis: Redis is an open source (BSD licensed), in-memory data structure store, used as a database, cache, and message broker. Redis provides data structures such as strings, hashes, lists, sets, sorted sets with range queries, bitmaps,
