Scribd
Upload
40 views3 pages

Database Creation and Management Guide

The document contains SQL code to create a database called "store" with tables for stores, products, customers, payment methods, purchase orders, and purchase order details. It inserts sample data and performs queries on the tables to retrieve, update, and delete data. Views are also created to select specific columns from tables.

Uploaded by

daniel centeno
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
40 views3 pages

Database Creation and Management Guide

The document contains SQL code to create a database called "store" with tables for stores, products, customers, payment methods, purchase orders, and purchase order details. It inserts sample data and performs queries on the tables to retrieve, update, and delete data. Views are also created to select specific columns from tables.

Uploaded by

daniel centeno
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

/* RETO 2.

Crear mi primera base de datos*/


/*Datos de identificación*/
/*Nombre Completo: Xavier de Jesus Tapia Cazares*/
/*Matricula: 20002455*/
/*Fecha de elaboración: 08 Septiembre 2021*/
/*Nombre del Modulo: Taller de bases de datos*/
/*Nombre del asesor: Orlando Daniel López Toledo*/
CREATE DATABASE store;
USE store;
CREATE TABLE store(
id INT NOT NULL PRIMARY KEY,
name VARCHAR(50) NOT NULL,
adress VARCHAR (50) NULL
);
CREATE TABLE product(
id INT NOT NULL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
price DOUBLE NOT NULL,
description VARCHAR(150) NULL
);
ALTER TABLE product
ADD store_id INT NOT NULL;
ALTER TABLE product
ADD CONSTRAINT fk_store_product
FOREIGN KEY (store_id) REFERENCES store(id);
CREATE TABLE customer(
id INT NOT NULL PRIMARY KEY,
first_name VARCHAR(50)NOT NULL,
last_name VARCHAR(50) NOT NULL,
email VARCHAR(50) NOT NULL,
password VARCHAR(50) NOT NULL,
adress VARCHAR(50) NULL,
phone VARCHAR(10) NULL
);
CREATE TABLE payment_method(
id INT NOT NULL PRIMARY KEY,
type_payment VARCHAR(50) NULL
);
ALTER TABLE payment_method
ADD customer_id INT NOT NULL;
ALTER TABLE payment_method
ADD CONSTRAINT fk_payment_method_customer
FOREIGN KEY (customer_id) REFERENCES customer(id);

CREATE TABLE purchase_order(


id INT NOT NULL PRIMARY KEY,
payment_method_id INT NOT NULL,
customer_id INT NOT NULL
);
CREATE TABLE purchase_order_detail(
id INT NOT NULL PRIMARY KEY,
amount INT NOT NULL,
price_per_unit DOUBLE NOT NULL,
product_description VARCHAR(50) NULL,
product_id INT NOT NULL,
purchase_order_id INT NOT NULL
);
ALTER TABLE purchase_order
ADD CONSTRAINT fk_purchase_order_payment_method

This study source was downloaded by 100000827204985 from [Link] on 06-20-2022 19:18:30 GMT -05:00

[Link]
FOREIGN KEY (payment_method_id) REFERENCES payment_method(id);
ALTER TABLE purchase_order
ADD CONSTRAINT fk_purchase_order_customer
FOREIGN KEY (customer_id) REFERENCES customer(id);
ALTER TABLE purchase_order_detail
ADD CONSTRAINT fk_purchase_order_detail_product
FOREIGN KEY (product_id) REFERENCES product(id);
ALTER TABLE purchase_order_detail
ADD CONSTRAINT fk_purchase_order_detail_purchase_order
FOREIGN KEY (purchase_order_id) REFERENCES purchase_order(id);

INSERT INTO store(id, name, adress) VALUES (459,"Electronic Technologies North",


"Blvd. López mateos #459");
INSERT INTO store(id, name, adress) VALUES (346,"Electronic Technologies
Central", "Av. Insurgente #346");
INSERT INTO store(id, name, adress) VALUES (549,"Electronic Technologies East",
"Blvd. Madero #549");

INSERT INTO product(id, name, price, description, store_id) VALUES(301,


"Computadora de escritorio-Todo en uno", 15499.00, "Todo lo que necesitas para este
regreso a clases", 549);
INSERT INTO product(id, name, price, description, store_id) VALUES(305, "Celular ET
- 15a", 3590.00, "Ideal para el trabajo", 549);
INSERT INTO product(id, name, price, description, store_id) VALUES(310, "Laptop -
Quinta generación", 9999.00, "Economica pero duradera, simplemente lo mejor del
año", 549);

SELECT * FROM product

UPDATE product
SET name="Celular ET2019 - 19xs", description="Ideal para el trabajo y la escuela"
WHERE id = 305;
UPDATE product
SET description="Todo lo que buscas en una computadora de escritorio"
WHERE id=301;

DELETE FROM product


WHERE id=305;

INSERT INTO product(id, name, price, description, store_id) VALUES(611, "HORNO


TOSTADOR", "1499.00", "Horno tostador de 4 rebanadas, acero inoxidable", 346);
INSERT INTO product(id, name, price, description, store_id) VALUES(512, "Freidora
con temporizador", 1590.00, "Puede cocinar cualqier alimento, desde papas fritas
hasta verduras y mucho más", 346);
INSERT INTO product(id, name, price, description, store_id) VALUES(815, "Batidora",
999.00, "5 velocidades, color rojo", 346);
INSERT INTO product(id, name, price, description, store_id) VALUES(531, "Fuente de
chocolate", 789.90, "mejora la limpieza gracias a sus piezas desmontables", 346);
INSERT INTO product(id, name, price, description, store_id) VALUES(912, "Maquina de
palomitas de maiz", 830.00, "La maquina ocupa aceite para la elaboración de las
palomitas", 346);
INSERT INTO product(id, name, price, description, store_id) VALUES(412,
"sandwichera", 459.00, "sandwichera para el desayuno", 346);
INSERT INTO product(id, name, price, description, store_id) VALUES(317, "Pantalla
Samart TV 50'", 8890.00, "Tamaño de pantalla real", 459);
INSERT INTO product(id, name, price, description, store_id) VALUES(679, "Camara de
seguridad", 1497.00, "Incluye soporte técnico 24/7", 459);
INSERT INTO product(id, name, price, description, store_id) VALUES(923, "Soporte
para TV", 279.00, "Adaptable a pantalla de 26 a 65", 459);

This study source was downloaded by 100000827204985 from [Link] on 06-20-2022 19:18:30 GMT -05:00

[Link]
INSERT INTO product(id, name, price, description, store_id) VALUES(873, "Laptop
1945", 15499.00, "Unidad de estado solido de 256GB y 8 de RAM", 459);
INSERT INTO product(id, name, price, description, store_id) VALUES(682, "Coche
Stereo- GPS", 1299.00, "pantalla tactil, bluetooth, no incluye DVD", 459);

SELECT * FROM product


ORDER BY price ASC;

INSERT INTO store(id, name, adress) VALUES (111,"sucursal", "blvd obregon #423");
SELECT * FROM store;
SELECT * FROM store
INNER JOIN product ON [Link] = product.store_id;

SELECT * FROM store


LEFT JOIN product ON [Link] = product.store_id;

SELECT * FROM store


WHERE id =(
SELECT store_id FROM product WHERE id =310
);

CREATE VIEW product_description AS


SELECT name, description, price FROM product;

SELECT * FROM [Link];

This study source was downloaded by 100000827204985 from [Link] on 06-20-2022 19:18:30 GMT -05:00

[Link]
Powered by TCPDF ([Link])

Common questions

Powered by AI

Allowing direct SQL operations can expose the database to security vulnerabilities such as SQL injection attacks, where an attacker can execute arbitrary SQL queries. Mitigation strategies include using prepared statements and parameterized queries to sanitize inputs, restricting database user permissions according to the principle of least privilege, and implementing rigorous input validation to ensure only expected types and formats are processed .

Not normalizing the 'customer' table can lead to data redundancy and update anomalies. For example, if a customer changes their email, the same email might appear in multiple records, leading to discrepancies unless all entries are updated. Additionally, storing potentially updatable information such as 'email' in multiple places can lead to inconsistencies if one update is missed .

Using indexes on the 'price' column in the 'product' table is critical for optimizing query performance, especially for operations like "SELECT * FROM product ORDER BY price ASC". Indexes allow the database to quickly locate and order records rather than scanning the entire table sequentially, thereby significantly reducing query execution time and improving efficiency .

Creating a view like 'product_description' simplifies database queries by providing a predefined query that selects specific columns from a table—in this case, name, description, and price from 'product'. This abstraction allows users to retrieve these common datasets without repeatedly writing complex queries, thus improving query manageability and performance .

Splitting data into separate tables like 'product', 'store', and 'customer' achieves better organization, reduces redundancy, and improves data integrity through normalization. This separation allows for more efficient queries, reduces chances of update anomalies, and makes maintenance easier since each table can be modified or queried independently while maintaining relationships through foreign keys .

Updates to product descriptions modify existing data, impacting data integrity by possibly affecting applications that rely on stable data. If applications use cached descriptions, updates can lead to inconsistency between the database and application data. Furthermore, if not all records that reference or utilize the product's description are updated appropriately, this may result in inconsistent views of product information .

Deleting a product entry like the one with id 305 can lead to referential integrity issues if there are records in related tables, such as 'purchase_order_detail', that reference this product ID. Without cascading deletes or adequately handling these references, this operation can leave orphaned records in detail tables, leading to data inconsistencies .

Constraints enhance data consistency by enforcing rules that the data in the database must adhere to. In this schema, primary key and foreign key constraints ensure unique identification of records and valid references across tables, thereby preventing data anomalies such as duplicates or referencing non-existent entities, which improves overall data consistency and integrity .

JOIN operations play a vital role in the database by allowing for the combination of records from two or more tables based on related columns. This is particularly important for retrieving comprehensive datasets that involve multiple entities, such as fetching all products related to a specific store using a JOIN between 'store' and 'product' tables. This allows for complex queries that efficiently retrieve related data while maintaining normalized table structures .

Foreign keys are used in the database schema to enforce referential integrity between related tables. For example, the foreign key 'store_id' in the 'product' table ensures that a product is associated with a valid store ID from the 'store' table, preventing entries that reference non-existent stores. This maintains data consistency and prevents orphan records .

You might also like