Database report oracle for supermarket system PDF

Preview

Summary

Complete database design and proposal for supermarket management system project with ERD design , tables, normalization and SQL queries...

Description

Fragrance flower

Supermarket Management system Database Design and Development

Database Design and Development Introduction In the flower sector, a company called scent flower group is working and giving services. They seek to supply fresh, gorgeous, and high-quality flowers in various cities. The company has been in operation for the past 50 years, and in addition to flower purchases, they also provide a variety of special occasion accessory products such as Teddy bears, chocolates, and vases. People prefer to buy gifts and flowers online rather than going to a store as technology advances, because it saves time. The company wished to replace their manual method with a new database system in order to bring their order system online. Companies may automate their ordering procedures with the help of online systems and databases, as everyone knows, thus developing a database system for a firm will make ordering and management suitable, useful, and trouble-free. Without understanding the comprehensive requirements concerning the system entities, their properties, types, and relationships between entities, database design and development is impossible. We'll start by looking at the specifics. So, there will be five tables in this database for entities like Customer, Order, Item, accessories, and flower arrangement. We need to create a logical data model, such as an Entity Relationship Diagram, to study the logical relationships between all of these entities. From the ERD diagram we can find out the cardinalities, relationship and attributes of entities. After that we will convert the ERD into tables to apply normalization. The redundancies and irregularities in the tables are removed using the normalization procedure. We'll begin database construction once all of the tables have been standardized and are redundancy-free. SQL (structured query language) is used in this database to build tables and attributes, as well as add constraints to attributes such as identifying Primary Key and Foreign Key. Following that, SQL queries will be used to retrieve the desired results from the database based on specified criteria.

ERD (Entity relationship diagram)

Figure 1: ERD

Normalization The redundancies and irregularities in the tables are removed using the normalization procedure. We'll begin database construction once all of the tables have been standardized and are redundancy-free. So, before we create the database, we'll analyze all of the tables to remove any anomalies or redundancies. To ensure that tables are redundancy-free, they should be filtered using different normal forms.

1st normal form Only one attribute should be assigned to each column in the initial normal form. Multiple attributes cannot be stored in the same field in any of the columns. As a result, the table below is in its most basic form.

2nd normal form Tables should be in 1st normal form before being converted to 2nd normal form, and there should be no partial dependencies between attributes.

3rd normal form There should be no transitive dependency between characteristics in the third normal form. For example, one non-primary attribute should not be dependent on another non-primary attribute.

BCNF Codd, Boyce is higher than the third normal form, the Normal Form is referred to as 3.5 NF. Before shifting to BCNF, the table should be in 3rd normal form, and if A and B have any functional dependencies, A should be the table's super key.

SQL Scripts (DDL and DML) and Relational mapping 1) CREATE TABLE Customer ( custNumber varchar(3) Primary Key Not Null, custName varchar(30) Not Null, custEmail varchar(20)Not Null, custPhone int, custCity varchar(20)Not Null );

CREATE TABLE RetailOrder( storeNumber int Primary key Not Null, OrderNumber int Not Null , storeCity varchar(20)Not Null, storePhone int, Manager varchar(20)Not Null, OrderMonth varchar(10) Not Null, OrderYear int Not Null, OrderType varchar(20), OrderTotal int, );

CREATE TABLE OrderItem( OrderNumber int Primary key Not Null, ItemID varchar(20)Not NULL, Quantity int );

CREATE TABLE FlowerArrangements( ArrangeID varchar(20) Primary key Not Null, ArrangeName varchar(20)Not NULL, style varchar(20), occasion varchar(20)Not Null, arrangePrice varchar(20), );

CREATE TABLE Accessory( AccessoryID varchar(20) Primary key Not Null, AccessoryName varchar(20)Not NULL, AccessoryPrice varchar(20), );

2) Insert Insert into Customer(custNumber, custName, custEmail,custPhone, custCity) Values ('C01','David Smith', '[email protected]', 09764443, 'Sydney') Insert into Customer(custNumber, custName, custEmail,custPhone, custCity)

Values ('C02', 'Adward', '[email protected]', 8765433, 'Perth'); Insert into Customer(custNumber, custName, custEmail,custPhone, custCity)

Values ('C03','Esra', '[email protected]', 6456777, 'Melbourne');

Insert into RetailOrder(OrderNumber,storeNumber,storeCity, storePhone, Manger, OrderMonth,OrderYear, OrderType,OrderTotal) Values(1500,1,'Sydney',

27747234,

'Kim', 'Dec', 2019,

45343623,

'InPerson',

Values(1501, 3, '400');

'Perth',

Values(1502, 2, ,'450');

'Melbourne', 23687326,

'Bill',

'Feb', 2020

,'Online'

values(1504, 5, '250');

'Adelaide',

'Jill',

'Mar', 2020,

'Phone',

76348235,

'Len', 'Jan', 2020,

'170');

Insert into OrderItem(OrderNumber,ItemID,Quantity) values(1501, 'F01', 2); values(1502, 'F01', 3); values(1503, 'F03', 3); values(1504, 'F03', 3);

'InPerson',

Insert into FlowerArrangements(ArrangeID,ArrangeName,style,occasion,arrangePrice) values('F01', 'Iris', 'Vase', 'Anniversary', '$80'); values('F02', 'Orchids', 'centrepiece', 'Congratulations', '$150'); values('F03', 'Lilies', 'Hamper', 'Thank you', '$100'); values('F04', 'Rainbow', 'Bouquet', 'Wedding', '$110'); values('F05', 'Sunflowers', 'Boxed', 'Get Well', '$60');

Insert into Accessory(AccessoryID,AccessoryName,AccessoryPrice) values('A01', 'Teddy bear', '$50'); values('A02', 'Chocolate', '$20'); values('A03', 'Basket', '$30'); values('A04', 'Plant', '$25');

Queries 1. Select* from FlowerArrangements;

2. SELECT custNumber, custName, custEmail,custPhone FROM Customer WHERE custCity LIKE 'Sydney';

3. SELECT OrderNumber,storeNumber,storeCity FROM RetailOrder WHERE storeNumbe r LIKE '2';

4.

5.

6. SELECT SUBSTR(AccessoryID,1,3) from Accessory;

7. Alter Table Accessory rename column AccessoryName TO AccessName;

8. Select OrderMonth,OrderYear, OrderType from RetailOrder where OrderMonth Like 'F eb';

9.

10.

Conclusion The company wished to replace their manual method with a new database system in order to bring their order system online. Companies may automate their ordering procedures with the help of online

systems and databases, as everyone knows, thus developing a database system for a firm will make ordering suitable, useful, and trouble-free. The redundancies and irregularities in the tables are removed using the normalization procedure. We'll begin database construction once all of the tables have been standardized and are redundancy-free. SQL is used to build tables and attributes in this database, as well as to apply constraints to characteristics such as designating the Primary Key and Foreign Key.

References [1] Deepak Thomas  ”Beginning PHP 4 Databases”, Wrox Press Ltd. Paperback-17, October, 2002.70-130 pp. [2] Matt Doyle, “Beginning PHP 5.3, 2 ndedition”, October 2009. 150-270 pp. [3] Luke Welling, Laura Thomson. Sams PHP and MySQL Web Development, 2nd edition, Paperback- 20 February, 2003. 105-209 pp. [4] W. Jason Gilmore “Beginning PHP 5 and MySQL 5 from Novice to Professional SECOND EDITION”, Jul 9, 2008.100-150 pp....