Create all the tables and relationships (including all primary keys, foreign keys, attributes and appropriate data types) shown in the ER diagram above. and Insert 3 records into each table. In the Item table, consider the first record as your favorite dress, second record as a gift that you have recently bought to someone, and the third record as your most favorite accessory that you are currently using; then insert the appropriate data. Similarly, insert some valid data into the other three tables.and Write an SQL statement to alter the Sales table such that the data types of Units_Sold and Dollars_Sold attributes are NUMBER(50) and NUMBER(200). and Write an SQL statement to update the third row of Item table: Item_Name as ‘Hat’, Brand as ‘Puma’, and Type as ‘Ferrari’. and Write an SQL statement to display the Branch_ID, Location_ID, Item_ID, Units_Sold and Dollars_Sold of each customer. Results should be in a descending order of Item_ID. and Write an SQL statement that displays the number of items, the sum of units_Sold and the sum of dollars_sold in each location. (Hint: Use group-by). and Write an SQL statement to commit the database. and Write an SQL statement to display all the tables’ data separately. (Hint: Use select and display data separately for each table. No need to use any joins.) and Write an SQL statement drop all the tables that you have created in this assignment. and Write an SQL statement to rollback the database. and Write an SQL statement to display all the tables’ data separately. (Hint: Use select and display data separately for each table. No need to use any joins.)and If you get any data displayed in question-11, list the names of all the tables that got displayed and tell us why there is still data existing despite you dropping all the tables in question-09. (Please answer this in comments).
Transcribed Image Text:### Database Schema Overview
#### Entity-Relationship Diagram Explanation
The diagram presented is an Entity-Relationship Diagram (ERD) designed to model the database structure for a retail-based sales system. Below is a detailed transcription and explanation of each entity and its attributes, as well as their relationships.
---
#### Tables and Attributes
1. **Time**
- **Primary Key (PK1)**: `Time_ID`
- **Attributes**:
- `Day`
- `Month`
- `Quarter`
- `Year`
2. **Item**
- **Primary Key (PK1)**: `Item_ID`
- **Attributes**:
- `Item_Name`
- `Brand`
- `Type`
- `Supplier_Type`
3. **Branch**
- **Primary Key (PK1)**: `Branch_ID`
- **Attributes**:
- `Branch_Name`
- `Branch_Type`
4. **Location**
- **Primary Key (PK1)**: `Location_ID`
- **Attributes**:
- `Street`
- `City`
- `State`
- `Country`
5. **Sales**
- **Primary Key (PK1, PK2, PK3, PK4)**:
- `Branch_ID`
- `Location_ID`
- `Item_ID`
- `Time_ID`
- **Foreign Keys (FK1, FK2, FK3, FK4)**:
- `Branch_ID`
- `Location_ID`
- `Item_ID`
- `Time_ID`
- **Attributes**:
- `Units_Sold`
- `Dollars_Sold`
---
#### Relationships
- **Time - Sales**: Each `Time` entity is related to the `Sales` entity through the `Time_ID`. This represents that sales transactions occur at specific times.
- **Item - Sales**: Each `Item` entity is related to the `Sales` entity through the `Item_ID`. This shows that multiple sales can involve various items.
- **Branch - Sales**: Each `Branch` entity is linked with the `Sales` entity through the `Branch_ID`. Sales are transacted at different branches.
- **Location - Sales**: Each `Location` entity is related to the `Sales` entity through the `Location_ID`. This indicates the geographic location where the sales occur.
---
#### Diagram Visualization
Organized collection of structured information, or data, typically stored in a computer system.
Expert Solution
This question has been solved!
Explore an expertly crafted, step-by-step solution for a thorough understanding of key concepts.
