ASM 1 1622: Database, Slides of Computer Science

Download ASM 1 1622: Database and more Slides Computer Science in PDF only on Docsity!

Higher Nationals in Computing

ASSIGNMENT 1

Learner’s name: Dong Sy Nhat Thanh

ID: GCS

Class: GCS1003A

Subject code: 1622

Assessor name: Dang Nguyen

Assignment due: Assignment submitted

ASSIGNMENT 1 FRONT SHEET Qualification TEC Level 5 HND Diploma in Computing Unit number and title Unit 04: Database Design & Development Submission date Date Received 1st submission Re-submission Date Date Received 2nd submission Student Name Student ID Class Assessor name 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 Grading grid

P1 M1 D

Assignment Brief 1 (RQF)

Higher National Certificate/Diploma in Computing

Student Name/ID Number: Unit Number and Title: Unit 04: Database Design & Development Academic Year: 2022 Unit Assessor: Tran Trong Minh Assignment Title: Database design Issue Date: 18 Jan 2022 Submission Date: Internal Verifier Name: Date: Submission Format: Format: ● The submission is in the form of an individual written report. This should be written in a concise, formal business style using single spacing and font size 12. You are required to make use of headings, paragraphs and subsections as appropriate, and all work must be supported with research and referenced using the Harvard referencing system. Please also provide a bibliography using the Harvard referencing system. Submission ● Students are compulsory to submit the assignment in due date and in a way requested by the Tutor. ● The form of submission will be a soft copy posted on http://cms.greenwich.edu.vn/. ● Remember to convert the word file into PDF file before the submission on CMS. Note: ● The individual Assignment must be your own work, and not copied by or from another student. ● If you use ideas, quotes or data (such as diagrams) from books, journals or other sources, you must reference your sources, using the Harvard style. ● Make sure that you understand and follow the guidelines to avoid plagiarism. Failure to comply this requirement will result in a failed assignment. Unit Learning Outcomes:

LO1 Use an appropriate design tool to design a relational database system for a substantial problem Assignment Brief and Guidance: You are employed as a Database Developer for a large IT consultancy company. The company has been approached by FPT Shop which is expanding due to the growth of the number of stores. FPT Shop is currently facing difficulties in dealing with managing the database from all shops on over country. It decided to develop a new database so that: users can register with their phone numbers as IDs and order or rate, comment for their bought devices, shop managers can take care for their stores and director board can view all data from all shops. You are tasked to select one of those systems to develop database for FPT Shop. Your tasks are to:  Work with FPT Shop to find out about current requirements for each system  Analyse the requirements and produce clear statements of user and system requirements.  Design a relational database system using appropriate design tools and techniques  Develop a fully functional relational database system, based on an existing system design.  Test the system against user and system requirements.  Produce technical and user documentation Part 1 (Assignment 1) Before you start the development process, your manager has asked you to produce a report for the CEO of FPT, containing:

1. Clear statements of user and system requirements. The system must have at least 3 user roles, including business processes and statistical reports for FPT Shop managers.
2. The design of the relational database system using appropriate design tools and techniques. It should contain at least four interrelated tables. You would prefer to produce a more detailed document, so you will produce a comprehensive design for a fully functional system which will include interface and output designs, data validations and cover data normalization. Your manager would like on the report your assessment of the effectiveness of the design in relation to user and system requirements.

Table of contents

• ASSIGNMENT
• Assignment Brief 1 (RQF)
  • Higher National Certificate/Diploma in Computing
• Task 1 - Statements of user and system requirements (P1)
  • 1. Introduction to database
  • 1.1. What is the database?
  • 1.2. Types of database
  • 1.2.1. Object-oriented Databases
  • 1.2.2. Hierarchical Databases
  • 1.2.3. Network Databases
  • 1.2.4. NoSQL Database
  • 1.2.5. Relational Database
  • 2. Introduction to RDBMS
  • 2.1. What is the RDBMS?
  • 2.2. Popular RDBMSs:
  • 2.2.1. Oracle
  • 2.2.2. MySQL
  • 2.2.3. PostgreSQL
  • 2.2.4. IBM DB2
  • 2.2.5. Microsoft Access
  • 2.3. Select the specific RDBMS to apply to the given scenario:
  • 3. Database Design base on the scenario
  • 3.1. Analyse the requirements
• Task 2 – Design the relational database system (P1 – M1)
  • 3.2. Logical Design
  • 3.3. Physical Design
  • 3.4. SQL Code
  • REFERENCES

Task 1 - Statements of user and system requirements (P1)

1. Introduction to database

1.1. What is the database? A database is a systematic collection of information or data that is organized and saved in an electronic format in a computer system. A database management system is usually in charge of a database (DBMS). A database system, which includes data and DBMS as well as the applications that go with it, is commonly referred to as merely a database. Figure 1: Illustration for Database To assist quickly process and query data, the most common types of databases in use today are frequently modeled as rows and columns in a sequence of tables. To write and query data, most databases employ structured query languages.

1.2.2. Hierarchical Databases Figure 3: Hierarchical Databases The hierarchical database model is best suited for use cases where the major focus of data collection is centered on a specific hierarchy, such as a group of employees reporting to a single company department. A hierarchical database's schema is defined by its tree-like architecture, in which data is stored as records in a root "parent" directory linked to numerous subdirectories, and each subdirectory branch or subrecord can link to several additional subdirectory branches.

1.2.3. Network Databases

Figure 4: Network Databases A network database model is one in which numerous member records or files can be linked to multiple owner files and vice versa. The model can be visualized as an upside-down tree, with each member's information serving as a branch connected to the tree's owner at the bottom. Relationships are essentially a net-like structure in which a single element can point to many data

items while also being pointed to by multiple data elements.

1.2.4. NoSQL Database

Figure 5: NoSQL Database NoSQL is a database management strategy that can handle a wide range of data models, including key-value, document, column, and graph models. A non-relational, distributed, flexible, and expandable database is known as a NoSQL database. In contrast to relational and SQL databases, which provide ACID (inheritance) transactional consistency, dependability, consistency, isolation, and durability), NoSQL databases have no database schema, data clustering, replication support, and eventual consistency. 1.2.5. Relational Database Figure 6: Relational Database A relational database is a collection of data elements that are linked by predetermined connections. These entries are laid down in a table format with columns and rows. Tables are used to keep track of information about the items in the database. Each column in the database has a different data type and a field that stores the attribute's actual value. The table's rows reflect a

2.2. Popular RDBMSs: 2.2.1. Oracle Figure 8: Oracle Oracle is the most widely used relational database system, invented by Oracle Corporation (RDBMS). Oracle isn't just an RDBMS; it also has Cloud, Document Store, Graph DBMS, Key Value Store, BLOG, and PDF Repository features. Recently. Oracle has unveiled self-management, which allows databases to be intelligent and self-managing. Oracle Database is a relational database management system (RDBMS). Data is stored in relational databases as tables with rows and columns. The qualities of an entity are represented by the columns of a database table, while the rows of the table store the records. An object-relational database management system is a relational database management system that supports object- oriented features including user-defined types, inheritance, and polymorphism (ORDBMS). Oracle Database has added an object-relational model to the relational model, allowing sophisticated business models to be stored in a relational database. 2.2.2. MySQL MySQL is the most widely used free and open source database on the planet. "Structured Query Language" stands for "Structured Query Language." SQL is the most widely used standardized database access language. You may be able to input SQL directly (for example, to generate reports), embed SQL statements in code written in another language, or use an implicit language- specific API, depending on your programming environment. The law of SQL. MySQL's primary characteristics: MySQL is a database management system. MySQL databases are structured in a relational format. MySQL is a free and open source database management system.

MySQL is a database server that is extremely fast, dependable, expandable, and simple to use. MySQL Server can be used in a client/server or embedded system. Figure 9: MySQL 2.2.3. PostgreSQL Figure 10: PostgreSQL PostgreSQL is an open source object-relational database system that uses and extends the SQL language, as well as a variety of other features, to securely store and grow workloads. the most complicated data The PostgreSQL Global Development Team manages PostgreSQL, which is written in C. PostgreSQL is known for its architecture, data integrity, powerful feature set, scalability, and commitment to the open source community behind the software to develop solutions on a constant basis. both efficient and inventive

MS Access is still one of the top ten databases for storing local data. In remote or centralized storage, access is rarely used. It's utilized for small databases on a local level. 2.3. Select the specific RDBMS to apply to the given scenario: We can observe that SQL Server effectively satisfies the parameters of the assignment circumstance based on its functionalities, advantages, and downsides. As a result of the following reasons:

• SQL Server optimization is done behind the scenes. Reduce database maintenance costs to improve your company's uptime. Boost data security and protect it while it's in use. Advanced resources can help you keep track of compliance. To optimize, a number of options and flexibility are used.
• SQL Server is Microsoft's native support.
• A safe environment
• Low ownership costs
• Effortless support for major corporations. Make the most of the time you have at your disposal. Take advantage of massive amounts of data. Integrate artificial intelligence (AI) into your business. It is no longer necessary to migrate data. Interacting with and studying visual data is possible. Analyze real-time activity statistics. 3. Database Design base on the scenario 3.1. Analyse the requirements I utilize a Microsoft SQL Server database to establish a database that meets the article's requirements. This design was created in response to a request from the FPT Shop. This database design article includes five tables, each with its own set of information to suit the requirements.

This article has certain prerequisites. The five panels are as follows:

• The Category Table, which lists each product category, makes FPT Shop simple to use. Customers will be able to access it soon.
• Product table: this table provides all information about the product.
• Maker table: this table offers information on the manufacturer of the product.
• Buyers can rate the product using the comments section.
• User table: this table keeps track of all product purchasers' details.

3.3. Physical Design Figure 13: Physical Design Explanation: Table name Fields Data type Constraint CATEGORY Category^ ID^ VARCHAR(^10 )^ NOT^ NULL^ PRIMARY KEY Category Name VARCHAR( 20 ) Product Product^ ID^ VARCHAR(^10 )^ NOT^ NULL^ PRIMARY KEY Product Name NVARCHAR(^20 )^ NOT^ NULL Product Price INT^ CHECK (ProductPrice > 0 AND ProductPrice < 1000000000 ) Product Detail TEXT NOT NULL Category ID VARCHAR( 10 ) NOT NULL FOREIGN KEY Producer ID VARCHAR( 10 ) NOT NULL FOREIGN KEY USER UserID^ INT^ NOT^ NULL^ PRIMARY KEY UserName NVARCHAR( 20 ) NOT NULL UserAddress NVARCHAR( 50 ) NOT NULL UserEmail NVARCHAR( 20 ) NOT NULL UserPhone INT NOT NULL

Feedback FeedbackID^ VARCHAR(^10 )^ NOT^ NULL^ PRIMARY KEY NameUser NVARCHAR( 20 ) NOT NULL Feedback TEXT NOT NULL UserID INT NOT NULL FOREIGN KEY ProductID VARCHAR( 10 ) NOT NULL FOREIGN KEY Producer ProducerID^ VARCHAR(^10 )^ NOT^ NULL^ PRIMARY KEY ProducerName NVARCHAR( 20 ) NOT NULL ProducerAddress NVARCHAR( 50 ) NOT NULL ProducerProductInformation TEXT NOT NULL ProducerPhone INT NOT NULL