The assignment covers various aspects of database system development, including designing databases, developing entity relationship diagrams, organizing data according to models, implementing querying statements, and testing the system. It also explores the technical and user documentation requirements for a database system.
Contribute Materials
Your contribution can guide someone’s learning journey. Share your
documents today.
Database Design
Secure Best Marks with AI Grader
Need help grading? Try our AI Grader for instant feedback on your assignments.
Table of Contents INTRODUCTION...........................................................................................................................3 MAIN BODY...................................................................................................................................3 P1 Design a relational database system of online fashion outlet shop by using the SQL management studio techniques...................................................................................................3 D1 The effectiveness of the design in relation to user and system requirements.......................8 P2 Develop the database system with the evidence of user interface, output data validation and querying across the different tables............................................................................................8 D2 Evaluate the effectiveness of database solution in the relation to system and user requirement................................................................................................................................19 P3 Implement a query language into the relational database system........................................19 P4 Test the system against the user and system requirements..................................................25 Test verification and validation process-...................................................................................28 P5 Produce the technical and user documentation.....................................................................28 D3 Future improvement that may require to ensure the continued effectiveness of database system.......................................................................................................................................30 CONCLUSION..............................................................................................................................31 REFERENCES..............................................................................................................................35
INTRODUCTION Data base design can be termed as the organisation of the data according to the model which helps in determining the type of data stored in it. Data base design involves classifying data and identifying interrelationship. This study will help in developing a database design for the fashion outlet in London in order to provide online facilities for the users. The database designing of the fashion outlet is aimed at developing the relational database system which allows the storage of customer details, order items, payment and employee details. This database design will help the retailing company in improving the operational efficiency and also provide easy access to the data and information regarding the day to day transactions. Moreover, the report will discover the appropriate entity relationship diagram which displays the relationship of different entities with each other along with the attributes and fields. It will help in developing the database with the suitable tool known as SSMS Or structured query Language. It will also enhance the understanding about development of the ERD to implement the Querying statement to insert, update, delete and modify the data into the tables. Testing the system is one of the important requirement so it will explore the testing phase of the database in order to carry out the system as well as user requirements. It will also present the technicle and user documentation. MAIN BODY P1 Design a relational database system of online fashion outlet shop by using the SQL management studio techniques. Entity relationship diagram for the entities along with the attributes. Entity relationship model is basically describes the interrelated things of interest in the specific domain of knowledge. ER is composed with the different entities types and specific relationship that already exist between the entities (Bjeladinovic, 2018). It is commonly formed torepresentthethingsaenterpriserequirementsinordertoperformbusinessprocess, consequently ER become an abstract of data model that define information or data structure which can be implemented in relational database.
Entity- An entity is a real world object that are represented in the relational database and it can be any object, person and class. Data and informations are stored in the form of entities in the DBMS. According to ERD, There are several type of entities used in the database system such as customer, payment, categories, cart and item etc. Attributes- It refers to the database component in the form of table and describe the instances in the row of DBMS (Hogan, 2018). Attributes are simply the one of non null cell in spreadsheet. It stores only one pieces of data about the object that represent buy the table. As per ERD, it has represented the attributes such as customer_id, name, phone no, payment_id, type etc. These are the different type of attributes shows in the database tables.
Paraphrase This Document
Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser
Primary Key- Primary key is a special type of entity and attributes that uniquely define the record in the database table. In customer tableï‚·Primary key: customer_id In online shopping siteï‚·Primary key : reg_no In payment tableï‚·Primary Key : payment_id In cart tableï‚·Primary key : cart_id In categories tableï‚·Primary key : category_id In item table ï‚·Primary Key : item_id In order table ï‚·Primary Key : order_id Foreign Key- Foreign key is basically the field in one table that uniquely determine the row of another and same table. It can use to create relationship between two tables in proper manner. It shows as references primary key into another table. Mapping into the relational Model Entities : Online Shopping site Reg_noNameWeb_address Customer Customer_idCustomer_nameCustomer_addressCustomer_phone Payment
Payment_idPayment_amountPayment_type Cart cart_idCart_total cost Categories Categories_idNameType Item item_idNameitem_price Employee emp_idemp_first name emp_last name emp_pho ne no emp_gen der emp_sala ry emp_add ress emp_DO B emp_hire date Order Order_idorder_nameorder_typeorder_status M1 Produce a design for a fully functional system which includes interfaced, ouput design s and normalization. Normalization Concept (1NF, 2NF and 3NF) Normalization is a type of process that help to organize the data and information in the database system. This process is used to minimize the redundancy from the relation and set of relations. It also help to eliminate the undesirable characteristics such as Anomalies, update, deletion and update etc. This process will help to divide a larger table into the smaller tables and also link between them using relationship. It can be divided into three different forms such as First Normal Form (1 NF) If a relation may contain the composite and multi valued attributes, it also violates the form normal form and relations is in the 1NF.A first normal form said to be 1 NF if each attributes of relation is atomic.
ï‚·In this way, each row in the table should be determined the primary key ï‚·There is no row of data repeating the column values and group. Second Normal Form (2 NF) To be in the 2NF (second normal form), a relation must be in the first normal form and it may not contain any type of partial dependency. In 2NF, it has no partial dependency and no prime attributes. ï‚·Each and every key attributes are determined by using primary key. ï‚·All type of sub type data which applicable to the multiple rows in the table and also remove or place in another table. New generated table should be related the foreign key. Illustration1
Secure Best Marks with AI Grader
Need help grading? Try our AI Grader for instant feedback on your assignments.
Third Normal Form (3 NF) In third norm form, it has no transitive dependency for the non prime attributes is in the 2NF (second normal form). This normal form should meet all the requirements of both 2NF and 1NF. In case, any column which are not matched to primary key so that it can remove them and also put the another separate table. D1 The effectiveness of the design in relation to user and system requirements. It is important to establish and documents the users requirements and needs so that can lead into the design process of system itself. User requirement must be included the overall description of every task when the system will support the functions. Without having the effective design, it is not possible to identify the actual processing of database system. System design phase is initiated and also ensure that the system requirements or capture in the repositories. It also mapping with the task and functions of system requirement. P2 Develop the database system with the evidence of user interface, output data validation and querying across the different tables. Online Fashion outlet shop has developed the database that manage all type of operations and their functionality. This is the graphical user interfaces when user can easily execute the queries and tables. However, The GUI provided by each database server where the online shop has own database sever to operates with the technical knowledge. Illustration2 Illustration3
This figure is representing the process of user interface when they can follow the specific steps to perform different operations and functions. Output Validation: Validation is basically process which allows the user to check the data which provided and also check the information before enter data into the system. In SQL sever management studio, It makes the assumption about the existing software development life cycle and adopt the new concept that help to meet the goals. The database should have technological tool for managing and retrieving the data in proper manner. It always maintain the user level security system in the relational database system. Illustration4: User interface design
Database Tables generated by using SQL Queries: Cart table: Illustration5
Paraphrase This Document
Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser
M2 Implement a functional database system which includes the system security and database maintenance. It is needed to maintain the system security of database system that help to store the information about the product, customer and categories etc. The designer has used an effective tool and technique to maintain the security aspects in the business process. There are some important security concerns such as ï‚·backup and recovery ï‚·maintenance strategy ï‚·data integrity ï‚·confidentiality D2 Evaluate the effectiveness of database solution in the relation to system and user requirement. The effectiveness of database solution is directly linked with the user and system requirement. It helps to the design approach to determine the logical conceptual ways to understand the need or requirement of users. P3 Implement a query language into the relational database system. SQL stands for structure query language which can use to design for managing the data in the relational database system (Link and Prade, 2019). This is the standard language for the relational database of fashion industry that help to perform differenttask in the tables such as update, delete, modify and retrieve data form the database. It is possible because of the SQL statements that contain various type of syntax to manage the data in the form of tables.
Secure Best Marks with AI Grader
Need help grading? Try our AI Grader for instant feedback on your assignments.
Query Solving: 1. Create a SQL statement to list all the employee and their full names, salary and gender. Illustration12
2. Create a SQL statement to show the title of employee and their department name. 3. Create a SQL statement to show the full names, gender who belong to the department number 'd004'. Illustration13 Illustration14
4. Create a SQL statement to show all the departments and their managers of department's. Illustration15
Paraphrase This Document
Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser
5. Create SQL Statement to show the list of department manager who were hired after 1986. Illustration16
6. Create a SQL statement to change any employee's date of birth. Assume the employee has just phoned in his last name. 7. Create a SQL statement to delete employee's record who belongs to department 'd004' and ID is 10003. Illustration17 Illustration18
8. Create a database view to show the list of employee first name, last name and salaries, departments. P4 / M4 Test the system against the user and system requirements. Software testing can be defined as investigation according to the expected model of the database with its actual functioning. It is mainly conducted to provide the stakeholders with information about the quality of the software product or service under the test. Database testing is defined as an activity to check weather the actual results match the expected result in order to ensure the defect free system (Matsuda and et.al.,2018). It helps in identifying the errors, gaps and missing requirements of the database system. There are commonly two types of testing in the software design that is User interface testing and Database Testing. ï‚·GUI testing:It Deals with all the testable items which are mainly open to the users. This testing is done on the view user interface by which the users interact with the software. Such UI includes Forms, Presentation, Graphs, Menu and reports etc. all these interface provides the easy accessibility to the user which allow the user to directly insert delete and update the data into the Back-end database. It can be usually presented via front -end tools such asVB, VB.net, VC++, Delphi-Front end etc. This testing includes the validationofthetextboxes,selectionofthedrop-downlist,calenders,buttons, navigation from one page to another etc. Illustration19
Secure Best Marks with AI Grader
Need help grading? Try our AI Grader for instant feedback on your assignments.
ï‚·Back-end or database Testing:It deals with the testable items that are generally hidden from the user. It commonly consists of the internal process and storage space such as Assembly, DBMS, SQL server, MYSQL, etc. It includes the database table testing, columns, server validation, Data duplication etc. There are various different types of testing are there such as: ï‚·Database schema testing:This type of testing mainly involves Validation process, verification of the table, views, columns etc. this type of testing is verifying the name of database, device services and allocated the enough space for each database in proper manner. It also check that the values entered in the table which are accurate (McDermott, Stephan and Gibson, 2018). ï‚·Procedural testing:This testing is basically follows the specific processes, procedures and practices that can use to ensure that the system and application is tested or validate before deployment. It must targeted the specific framework to maintain the security in overall system in effective manner. ï‚·Functional testing :Functional testing is a type of software that can test against the functional requirements and specifications. It help to verify that each functions in the application operates in the proper manner. This testing is generally form of automated testing that deals with the functionality of application. This Software is mainly designed for the Fashion Outlet retailing in order to establish the online services of the company through the development of the online website (Singh, Bhadauria and Gurung, 20189). This system is aimed at checking the schema, tables, triggers, etc. of the database under the testing process. It may involve the creation of the complex queries in order to check load and stress the database responsiveness. Mostly it checks the consistency and integrity of the of the data.
Test DescriptionExpected outcomeActual Outcome Schema TestingTheexpectedoutcomeof schematestingtoverifythe entire schema which enter the correct value in it. DatabaseTableandColumn Testing It display the entire values that shouldbegeneratedthrough the SQL statements. Stored procedure testingIt will create the database table tostoreallthedataand informationintheformof databasetablesinproper manner.
LoginandUsersecurity Testing Test verification and validation process- Verification is a process which includes the design, coding, documents and scripts of the system. This process is evaluating the work products of the development phase of database to determine whether they meet the requirements or needs. It also ensures that design pattern full fill the business needs in proper manner. It easily analyse the data or information that can enter in the Database system (Son and et.al.,2018). Verification process makes a test cases which may prepare for the verification of their data and information. Validation is a dynamic mechanism of database system that validates the actual outcome or results. It uses the different method such as white box testing, black box testing and non- functional testing. Validation process can easily find the bugs in the overall system. P5 / M5 Produce the technical and user documentation Technical documentation refers to the type of documents that describe the functionality, architecture of technical procedure of database development. It is the core aspects of database design when divided into the sub division of the development. Users are generally focus on the development aspects to collect the necessary resources which requires for meeting the specific goals and objectives. User use the database development life cycle on the basis of model: ï‚·It has separated the development of database that is creations and specifications of the schema to define data in the DBMS. ï‚·It also use the three schema architecture as on the basis for distinguish the activities with the data schema.
Paraphrase This Document
Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser
Requirement gathering-It is the first step for gathering the requirements when the user have to the determine the customer (database user) to understand their proposed system. It also obtain the document the data and functional requirements. It is also establishing the requirement which involves the consultation with the agreements among all the users as persistent data. Data administrator plays important role in maintain the information in proper manner. Data requirements documents is basically used to confirm and understanding the need with users. Analysis-It should be began with the statements of data requirement and then produce the conceptual data model. In this way, it easily obtain the details description of entities and attributes that describe the database design (Thai and et.al.,2018). This phase is analysing the user requirement in both high level and low level properties. In database documentation, the conceptual model is based on the formal representation of what type of data should contain in database so that constraints must be satisfied. Logical design-the database development start with the logical model that help to produce the specification of logical schema. It will be determining the data type of DBMS system and representation the independent of any system. Relational data system is describe the detailed specification,tables,columns,constraintsthatneedtosatisfy the description of Illustration20: DBMS Life cycle
information in proper manner. A good conceptual data model is necessary to explain the database properties, but doesn't direct transform to the SQL tables. SQL (DDL) statement can be needed to use the database which implement the scripts to meet the user requirements. D3 Future improvement that may require to ensure the continued effectiveness of database system. For making the system adaptive to change the environment, it will be necessary for the online shopping system or database system that can help to create the tables. It is performing different functions in the future to satisfy the requirements, it can create separate SQL queries, statements for the purpose of database creations.and columns which combine with other relational. For the further enhancement to handle the nested queries, sub queries such as select, create, insert etc. These are the different statements use in the Structural query language.
CONCLUSION Database system is a process and method to organize, retrieve, update and delete the information in the database system. It is also determined the organisation of the data according to the model which helps for managing the data and information in proper manner. Database design involves classifying data and identifying interrelationship. Moreover, it is discussed about the entity relationship diagram that shows the specific relationship between the different with each other along with the attributes and fields. It is used the SSMS platform for developing the database system in proper ways. It summarised that enhance the understanding about development of the ERD to implement the Querying statement to insert, update, delete and modify the data into the tables. Testing the system is one of the important requirement so it explore the testing phase of the database in order to carry out the system as well as user requirements. It also present the technical and user documentation.
Secure Best Marks with AI Grader
Need help grading? Try our AI Grader for instant feedback on your assignments.
REFERENCES Book and Journals Bjeladinovic, S., 2018. A fresh approach for hybrid SQL/NoSQL database design based on data structuredness.Enterprise Information Systems.12(8-9). pp.1202-1220. Hogan, R., 2018. A practical guide to database design. Chapman and Hall/CRC. Link, S. and Prade, H., 2019. Relational database schema design for uncertain data.Information Systems. Matsuda, K. and et.al.,2018. Design paper: Japan Endoscopy Database (JED): A prospective, largedatabaseprojectrelatedtogastroenterologicalendoscopyinJapan.Digestive Endoscopy.30(1). pp.5-19. McDermott, T.E., Stephan, E.G. and Gibson, T.D., 2018, April. Alternative database designs for thedistributioncommoninformationmodel.In2018IEEE/PESTransmissionand Distribution Conference and Exposition (T&D)(pp. 1-9). IEEE. Singh, A., Bhadauria, V. and Gurung, A., 2018. A Problem-Solving Based Approach to Teaching Database Design. Son, Y. and et.al., 2018. Design and Implementation of SSD-assisted Backup and Recovery for Database Systems.IEEE Transactions on Knowledge and Data Engineering. Thai, S. and et.al.,2019. Concrete-filled steel tubular columns: Test database, design and calibration.Journal of Constructional Steel Research.157. pp.161-181.
1
Secure Best Marks with AI Grader
Need help grading? Try our AI Grader for instant feedback on your assignments.