Data Definition and Data Manipulation Language

This retailer shop extensively deals with the sale of electronics such as laptops, mobile phones, drones, etc. The business enterprise has experienced significant growth for there is an increased number of clients that access the business and the business comprehensively focuses on engaging in online sales. Due to the growth in the retailer shop, there is increased concern from the customers for they highly advocate for the provision of electronic purchase model from the business. This changes or increases the perspective and dealings of the business. The business currently uses a website that is unstable or unreliable for it is a simple Website that cannot effectively support the dynamic interaction on the website or for online purchases.

Currently, the database that the business is incorporating in the physical store has some issues and it cannot be effective in the business transaction. The database system cannot rely on a hundred percent. The current database cannot integrate the online database system information and the current data entry system that is used to support sales made in the local store. There are gaps in the database that needs to be addressed. The business requires that we establish a database that secures, integrates, and configure information. (Elmasri & Navathe, 2008)

The purpose of the Shamraz Enterprise business database is to effectively manage the daily transactions, integrate all the data, store and maintain customer records, keeps a record of the entire Shamraz employees and as part of the business information center.

The explicit goal for the database is to minimize redundancy and store all business dealings. The reason for establishing the database is due to the significant retail progress, and the currently available database in the organization is not adequately effective.

The database is expected to integrate all sales from the enterprise both from online sales and from physical sales.

To maintain the customer’s records in the enterprise.

To transform data collected into reliable information in the firm.

Provide a mechanism of data management within the entity, limiting the number of authorized data dealers.

Finally, the goal of the database is to assist in the enforcement of the enterprise’s rules of business all over the world.

The Microsoft SQL Server Express Edition collects data from the business enterprise. This database is effective in this business for it associates and describes data associated with the business. This is very essential for a retail store that has a simple Web site, but it is not clear that the system can support dynamic interaction on the Web site or online purchases. The new database is effective for it can support dynamic interaction on the Web site or online purchases and integrate data made in the local store. The database is effective for integrates unrelated information. This information is essential for it provides better insights on the market trend in the market. From the database, it is easy to track the number of customers visiting the local store or using the electronic purchase model.

Since there is sales growth and different aspects of business in the enterprise the database is very essential for the business since it transforms data into information. The Microsoft SQL Server Express Edition is effective in this business for their system contains tools for data analysis and aggregation. From the data provided in the system, the management in the enterprise can use information obtained from the database in decision-making. Data from the system will provide vital information necessary for the implementation of essential gaps in the enterprise.

When the database collects information on the business cultures and trends in the store, the management can use data from the database to enforce the rules that have been established for their business practices. The database is vital for the enterprise for it guarantees that customers can easily access information of the business and all commodities that can be purchased electronically. The store can establish the number of customers using the online purchase method and physical purchase model. (Elmasri & Navathe, 2008)

The Microsoft SQL Server Express Edition is the proposed system for the enterprise. The system aligns with the strategic goals of the organization and mission statement. The desired organizational goals and mission can be attained using Microsoft SQL Server for the system enhances performance in the enterprise. The firm seeks to integrate data from both online and physical customers. Since the retail shop, has a weak website, in incorporating and protecting data from the growing clientele the use of the Microsoft SQL Server system is vital due to its optimized data storage. The database is convenient for the growing business for it is easy to manage and configure data.

The Microsoft SQL Server is very essential and vital for it aligns with the strategic goals and mission statement of the business enterprise. For the Microsoft SQL Server, it is easy to configure data provided during business transactions in the enterprise. Management and use of the database are easy to manage for it does not require frequent updates for the Microsoft SQL Server automatically updates itself and does not require any specific installing toolkits. Since the current physical database is flawed, Microsoft SQL is convenient to modify for the organization to effectively manage data in the business. With the database, the business can effectively manage daily transactions.

The Microsoft SQL database is essential in the objective and goal attainment of the business enterprise for it optimizes data storage. The database is essential in maintaining the customer’s records in the enterprise for it allows easy data management with minimal maintenance and troubleshoots. In the database, it is easy to store data for a long with no troubles. The explicit goal for the database is to minimize redundancy and store all business dealings. The database saves time for the employees since the information from the database analysis and integrates information. From the information obtained, we can use the information to enforcement of the enterprise’s rules of business all over the world.

Microsoft SQL Server aligns with the goals of the firms for it increases data security. The software will allow the business to protect and integrate data from physical customers and online customers. This system will allow the management to secure the data of all the customers. This will build the trust of the customers towards the business enterprise. The security of data in the business is vital for the business since it contains very sensitive data on the clients. (Gillenson, 2012)

The database contains tools for analysis and aggregation that help put data into context. The relational database is the standard form that will be applied in the retail shop. I will organize data in tables and lists. Every table that will be applied will have a defined nature of data. The relational database is commercially used for it organizes data in relations. For the effective documentation of the database, each table will consist of a column and row. The columns are referred to as the attribute while a row is a tuple. The use of a relational database is effective for the retail shop for its easy retrieval and ability to store a huge amount of data. SQL in this instance is the preferred language. In the retail shop, the data that is required looks at deliverables such as subjects of interest and the business rules. Into subject-based tables, I will divide the data required.

The following are the business rules that will be enforced by the database system. In the database system. Several constraints are added to the attributes in the tables. The rules for the retailer shop dictate what RDBMS allows the personnel to do to a particular attribute. For high-level data integrity, the rules provided for the retail shop must be adhered to to ensure accessibility and accuracy. The rules for the data of the retailer shop are;

· Each product is assigned to a maximum of one category.

· A person who has placed at least one order is a customer.

· All products have a minimum reorder level.

· A customer can only be documented once.

· That each order detail is specified for a particular product.

· Each record assigns a value to each feature.

· restricts an entity to be related to only of several other

The data structure in this database will consist of views, data tables, and indexes. In the data tables, the record with a unique identifier is contained in the row section. This unique identifier is the key and the column will hold the attributes of the data. For easy identification of relationships, each feature is assigned value by each record. In a retailer shop, there are several attributes; customers, orders, products, Price, inventory identification, and date of purchase. For the column, it should contain the record of the unique identifier. In this case, in the creation of a customer's table, the primary keys will be the customers' ID and for the products table, I will apply the product code as the primary keys. I will use the customers' identification instead of the customer's name for different customers can share the same name. There should be no duplicate value when identifying unique values for the column. For effective recording each primary key has values. In the separation, one will have a clear indication of commodities available and quantity. (Elmasri & Navathe, 2008)

The creation of the customers' table, it will consist of columns of the first name, the last name, address, city address, phone number, zip code, and city. The name, phone number, and address cannot be used as the primary keys for they may change and for the name, more than one customer can share a similar name. It is important and beneficial for the retail store to use the customers' ID, as the primary key for the other entire valuable is not unique. In identifying the customers, the customer ID will be constant and unique for every customer. The creation of a fact-less auto-incensement number is ideal for the customers' table. For entity integrity, there is a need to ensure that the value is not set to null or the primary key is unique.

After defining relationships, and entities the last step is the identification of the data type that each attribute will need. In the building of the retailer shop database, the data type is very critical for it is declared when creating the table. In this database, we are going to apply various data types. We will incorporate DATE, INT and VARCHAR. For INT data type the integer, represent the ID fields of the various accessories. This forms the primary keys of the table. DATE data type represents the date field that is stored in YYYY-MM-DD format. The VARCHAR is applied in the storing of varying length data and it can store more than fifty characters.

Customers’ table

This project fulfills the various missions and goals that the retail shop had. In minimizing redundancy and storage, for all businesses the use of the system is vital. Relational databases are important for the business for we will use the model to track, purchase, and process inventory. This is because it provides data that is secure, consistent, and manageable. From the database, the manager of the retail shop will have the capacity to comprehensively analyze the progress of the shop. The maintenance of the customer's records in the enterprise relational database is critical.

The reason for establishing the database is due to the significant retail progress, and the currently available database in the organization is not adequately effective. The explicit goal for the database is to minimize redundancy and store all business dealings. This system is important for it allows the administrators to make physical changes or edit the stored information without affecting the logical data structure of the retailer shop. In the relational model, there are minimal ambiguities due to duplication. To avoid ambiguities in data retrieval in the relational database there is no use of duplicate rows. This is important to such an institution for they deal with different types of accessories and customers. (Elmasri & Navathe, 2008)

In DDL statements, in the creation of table structures and database SQL statement CREATE is utilized. CREATE/ALTER TABLE/ DROP and CREATE DATABASE are the major statements in SQL DDL. For the retail store in the new system, I will create a database named the SHAMRAZ E. This database will be created by the SQL statement CREATE DATABASE SHAMRAZ E. After the completion of creating the database, the next step will be the creation of database tables. I will apply the general format for the creation of the database tables in the system. CREATE TABLE command is the most effective format in the creation of database tables. I will apply the following format in the creation of different database tables for the retail shop.

CREATETABLE<tablename>

(

Column Name, Data type, Optional Column Constraint,

Column Name, Data type, Optional Column Constraint

Optional Table Constraint,

);

In the database for the retail shop, the tablename represents database tables’ names such as for the Customer’s table, inventory table, or sales and purchase table. In the creation of the database tables, each table will be divided into three parts. The tables are divided into Column Name, Data type, and Optional Column Constrain. For the column’s name for the customer’s table, we will apply a unique identifier. In this case, the unique identifier is the customer’s Id that is unique for each customer. In the data type for the retail shop, the data is either user-defined data type or system data type. In this database for it is a retail shop dealing with the sale of electronics and accessories, the data type size is estimated to Numeric (8, 2) and CHAR (35).

In the initialization of a value for a new record, I will apply DEFAULT, PRIMARY KEY, NULL, UNIQUE, and NOT NULL as the Optional Column Constraints. In the indication that null values are allowed it will be indicated by the column constraint NULL. The column constraint NULL indicates allows one to create a row without a value for the column while for the NOT NULL column constraint that a value must be provided when a new row is created. For the NOT NULL column, it cannot be left blank at any point. (Gillenson, 2012)

In the manipulation of data from the table, I will use the UNIQUE constraint. The constraints provide an incremental and unique value for the column. They serve as unique identifiers in the tables.

Using SQL DDL, we can easily alter the table statements to drop and add constraints. There is the use of ALTER TABLE statements that allows for the removal of columns. The ALTER TABLE statements allow for the addition of a constraint and it verifies violations. In the removal of tables from the database, the DROP TABLE statements are applied for the system.

In the modification and query of the database, there is the application of The SQL data manipulation language (DML). There are rules for the SQL DML statement that are used in database creation. For user-defined words, we will use the lower case letter, where a part has several clauses, that appear on separate lines, and for every clause, it begins on a new line.

The SELECT command gives a person access to extract data based on specific criteria and from tables. The selection sequence for this retail shop is as follows:

SELECT DISTINCT item(s)

FROM table(s)

WHERE predicate

GROUP BY field(s)

ORDER BY field

Using the above selection sequence, we can generate the email address of customers selected. I will need a frequent update on the addresses of customers shopping with us.

I will apply the UPDATE statement in modifying and making changes to the existing data. There will be frequent changes in the database for it deals with changes in quantity and prices. In updating the price of a product, using the unique identifier for that product in this database there is the application of the UPDATE statement or command. I will use the UPDATE statement to update the price of the product. I will use a unique identifier in selecting the value of the prices that I need to update.

In addition, of rows to a table in the database, I will apply the INSERT statement in inserting 20 records into each table for testing purposes.

In the Deletion of an entire order using the unique identifier for the laptop order, I will use the DELETE statement that permanently removes the row record set. In the deletion of a record on a row in the database, the unique identifier limits the deletion. The unique identifier for the laptops in this case is its inventory number, which is the unique identifier for the database. The syntax for the deletion is as follows;

DELETE FROM Orders

WHERE ORDER_ id IN

SELECT ORDER id FROM laptop

Aggregate functions perform a calculation on a set of values and return a single, or summary, and value. There are built-in such as MAX, COUNT, MIN, SUM, and AVERAGE. Using the five built-in options, we can effectively and correctly calculate the following-:

· Total revenue (sales) per month, grouped by customer

· Total revenue (sales) per month, grouped by product

· Total count of products, grouped by category

· Total product sale, grouped by category

· Average profit per month

· Minimum sales, grouped by category

The COUNT option will be extensively used in obtaining returns of the number of values in the column. In calculating the Average profit, we will apply the AVERAGE option and MIN for the minimum sales grouped by category.

Since there is, an increased number of customers purchasing products from the store frequently, data manipulation is very central to the business. There is a need to frequently update the database for the record varies with time. With the increased number of activities in the store manipulation of data is critical during updating, deleting, and selecting data from the database tables. This manipulation allows for the adjustment of records in the database. This section is very important for it protects the integrity of the data recorded. To reduce errors the systematic format for the recording of data does highlight mistakes thus making it more effective.

The five aggregate functions provided allow the management to calculate the Total revenue (sales) per month, grouped by customer, Total revenue (sales) per month, grouped by product, and the Total count of products, grouped by category. With the increased activities in the store, these functions will assist the management in the calculation. This limits the margin of error in total calculations. This type of database is very effective for a business entity. (Elmasri & Navathe, 2016)

Elmasri, R., &amp; Navathe, S. (2008). Fundamentals of database systems. Upper Saddle River, NJ:Pearson.

Elmasri, R., &amp; Navathe, S. (2013). Fundamentals of database systems: Models, languages, design, and application programming. Boston: Addison wesley.

Elmasri, R., & Navathe, S. (2008). Fundamentals of database systems. Upper Saddle River, NJ: Pearson.

Elmasri, R., & Navathe, S. (2013). Fundamentals of database systems: Models, languages, design, and application programming. Boston: Addison wesley.

Elmasri, R., & Navathe, S. B. (2016). Fundamentals of Database Systems, Global Edition. Pearson Education UK.

Elmasri, R., Navathe, S. B. (2016). Fundamentals of Database Systems, Global Edition. Pearson Education UK.

Gillenson, M. L. (2012). Fundamentals of database management systems. Hoboken, NJ: J. Wiley &amp; Sons.

Gillenson, M. L. (2012). Fundamentals of database management systems. Hoboken, NJ: J. Wiley & Sons.

Negi, (2019). Fundamentals of database management system. Place of publication not identified: BPB PUBLICATIONS.

NEGI, D. R. M. U. K. E. S. H. C. H. A. N. D. R. A. (2019). Fundamentals of database management system. Place of publication not identified: BPB PUBLICATIONS.

Sumathi, S., & Esakkirajan, S. (2007). Fundamentals of Relational Database Management Systems.

Sumathi, S., &amp; Esakkirajan, S. (2007). Fundamentals of Relational Database Management Systems.