Mysqlproject

Statement of the project An E-commerce company wants to set up a database managing the stocks of their online sales (it can be specialized in clothes, books, household appliances, interior decoration...). To organize the online store, the company defines a catalog of categories where each category has one or more products. However, a product can only belong to one category at a time. At the supply level, the same product can be purchased from different suppliers. A customer can place one or more purchase orders that will be delivered on a specific date. A purchase order is composed of one or more products where for each one, a discount can be defined (in percentage of the selling price). Any purchase order can be delivered at once but a delivery can contain several purchase orders. The database will respect the following business rules: ● The delivery date of a delivery note must be on or after the order date. ● For a given product in a purchase order, the sum of the quantities of the associated deliveries must be less than or equal to the quantity of the purchase order. ● The sum of the products to be delivered cannot exceed the quantity in the stock. Work to be done The project is to be done per group of 3 students. Therefore, groups of four people are not allowed. If your class has a number of students not divisible by 3, one or two groups of 2 are allowed. The final creation of the groups will be finalized at the end of the second class. 1. Definition of the conceptual data model From the statement, express a conceptual data model using the Entity-Association model to allow the E-commerce company to manage inventory for their online sale. 2. Definition of the relational model and implementation with a DBMS You will deduce, from the proposed modeling, a relational model of the database, by specifying in a concise and precise way the passage to relational. Then, you will implement the relational schema with a DBMS of your choice. You will define the domain constraints. You will insert, in the database, a minimum number of tuples, but sufficient to illustrate the possibilities of the application and test the queries 3. Predefined query requests You will propose, at least, 10 relevant queries of various difficulties in relation to the general application, and you will give the expression in SQL. The queries can be stored as a view or as a text file, so that they can be reused. Project progress and deadlines This project will result in a clear, concise and comprehensive report, containing at least the following sections: ▪ The definition of a data dictionary describing the entities of the schema, the details of each entity and the relationships between them. ▪ The precise description of the transition to the relational model. No more than two pages of explanation. ▪ The SQL script to create the database schema. ▪ The expression in English and in SQL of the predefined queries (see item 3 of the assignment). ▪ A conclusion describing the limitations of your work, as well as an estimate of the time spent per person and the distribution of tasks. You should spend no more than 10 hours per person on this project. It is not necessary to put the script of insertion of tuples. Although comprehensive, your report should remain sober: avoid overly convoluted presentations and unnecessary speeches. Comments should introduce, highlight and justify your work. Be concise. The report is due on Friday, Mars 17th, 2023 before 12am. Please write legibly the name of each member of the group on the cover of your report. An oral defense, only on the SQL code of the schema and the queries will be done, during the last session: the week of the 20/03. The project will have to be finished by this date. The objective of the defense is to verify the progress of the work and the involvement of each member of the group in the project.