代做Individual Assignment#3A代做数据库编程

日期：2024-04-20 06:23

Instructions

Individual Assignment#3A

Description:

Create Database and load Data - create the physical database and load it with "real" or "realistic" data.

Instructions:

a. Correct relational model as per feedback provided for individual assignment#2A.

b. Generate the DDL code from the relational model of your individual project.

c. Create tables along with column comments, Primary Keys, and foreign keys on Oracle Live SQL.

d. Populate all tables with appropriate data - must have adequate data to support the problem / opportunity of your business case (7 to 10 records for Parent tables, 10 to 15 records for Child tables, 15 to 20 records for intersect tables)

e. Run following dictionary queries and capture the screenshots.

REM: ??List of Tables

select table_name

from user_tables;

REM: ?List of Table Columns

select table_name, column_name,column_id

from user_tab_columns

order by table_name,column_id;

REM: ?List of Table Column Constraints

select table_name,constraint_name,constraint_type,search_condition,index_name,r_constraint_name,delete_rule

from user_constraints

order by table_name;

REM: ?List of Table Column Comments

select table_name,column_name,comments

from user_col_comments

order by table_name;

f. For EACH tables of your database schema run following to show data. Replace with actual table name.

select ?count(*) ??from ;

For example, ??SELECT ?count (*) ?FROM AP_PAYMENT;

Submission:

Submit following in a single PDF document (your ?initial_IAS3A.pdf with screen shots copied to the same document. Each team member needs to submit the assignment.

1. Title page

2. Executive Summary

3. Logical Model

4. Relational Model

5. DDL script. generated from Relational model

6. Resulting screen shots from the dictionary queries in instructions "e" above

7. Resulting screen shots from the queries in instructions "f" above

Sumit your assignment with file name format "asp13_ISA3A.pdf" ?(replace asp13 with your net id)

Submission Feedback

Overall Feedback

Please make following changes before the next assignment.

Address is the composite attribute and needs to be resolved?Name is the composite attribute and needs to be resolved.?1:1 relationship should be resolved? in relational model?FK column names should be renamed to match PK column names of their parent? tables

In logical model, attributes should not be multiple words

Feedback：

ISA2A

Executive summary

This meticulously designed and comprehensive database serves as the backbone of Tesla's operational infrastructure, playing a critical role in enhancing various aspects of the company's operations. Primarily, it focuses on bolstering operational efficiency, streamlining customer relationship management, optimizing inventory control, and ensuring consistently high service quality standards. The database architecture is intentionally crafted to be scalable, allowing for seamless integration of new product lines or business models as Tesla continues to expand its offerings and market reach.

One of the key features of this database is its intricate network of relationships between different entities, which provides a holistic view of Tesla's operations. From the manufacturing processes to post-sale services, every aspect of Tesla's business is interconnected within the database. This interconnectedness facilitates the smooth flow of data across departments, enabling informed decision-making at various levels of the organization. For example, data from production can inform. inventory management, which in turn influences customer service strategies, ensuring a cohesive and efficient operation.

Moreover, the database serves as a repository of invaluable insights derived from extensive data analysis and modeling. By leveraging advanced analytics tools, Tesla gains deeper understanding into market trends, consumer behavior. patterns, and operational performance metrics. These insights are instrumental in guiding strategic decision-making processes, driving product innovation, and optimizing resource allocation. For instance, data on customer preferences can inform. the development of new vehicle features, while insights into production efficiency can lead to improvements in manufacturing processes.

In addition to its role in supporting day-to-day operations, the database also plays a crucial role in ensuring data security and compliance with regulatory standards. Advanced encryption techniques and robust access controls are implemented to safeguard sensitive information and protect against potential cyber threats. Furthermore, the database architecture is designed to facilitate seamless integration with third-party systems and applications, enabling collaboration with suppliers, partners, and other stakeholders within the automotive ecosystem.

Overall, this comprehensive database represents a strategic investment in Tesla's technological infrastructure, underpinning its mission to accelerate the world's transition to sustainable energy. By harnessing the power of data-driven insights and fostering a culture of innovation, Tesla remains at the forefront of the automotive industry, continuously pushing boundaries and shaping the future of mobility.

Logical Model

Relational model

Assumption

-?Customers to Orders: One-to-many relationship. A single customer can place multiple orders.

-?Orders to Vehicles: Many-to-one relationship. Multiple vehicles can be associated with a single order, especially in cases of fleet sales or multiple purchases by a single customer.

-?Vehicles to Production: One-to-one relationship. Each vehicle has a unique production record detailing its manufacturing process.

-?Vehicles to Maintenance Records: One-to-many relationship. A vehicle can have multiple maintenance records from various service visits.

-?Service Centers to Maintenance Records: One-to-many relationship. A service center can perform. maintenance on multiple vehicles, generating several maintenance records.

-?Parts to Part Inventories: One-to-many relationship. A single part type can have multiple inventory records across different locations or suppliers.

-?Suppliers to Part Inventories: One-to-many relationship. A supplier can supply multiple types of parts, each with its own inventory records.

- Maintenance Records to Parts: Many-to-many relationship (realized through an associative entity, not listed as a core entity here). A single maintenance activity can require multiple parts, and a part can be used in multiple maintenance activities.

DDL code

-- ?Generated by Oracle SQL Developer Data Modeler 23.1.0.087.0806

-- ??at: ????????2024-03-26 13:10:34 EDT

-- ??site: ??????Oracle Database 21c

-- ???type: ?????Oracle Database 21c

-- predefined type, no DDL - MDSYS.SDO_GEOMETRY

-- predefined type, no DDL - XMLTYPE

CREATE TABLE bookingorders (

order_id ?????????????NUMBER(1, 1) NOT NULL,

order_date ???????????DATE NOT NULL,

total_price ??????????NUMBER(2, 2) NOT NULL,

customers_customer_id NUMBER NOT NULL

);

ALTER TABLE bookingorders ADD CONSTRAINT bookingorders_pk PRIMARY KEY ( order_id );

CREATE TABLE customers (

customer_id ??NUMBER NOT NULL,

name ?????????VARCHAR2(20) NOT NULL,

email_address VARCHAR2(30) NOT NULL,

phone_number ?VARCHAR2(30) NOT NULL,

address ??????VARCHAR2(30) NOT NULL

);

ALTER TABLE customers ADD CONSTRAINT customers_pk PRIMARY KEY ( customer_id );

CREATE TABLE maintenance_records (

maintenance_record_id ????????????NUMBER NOT NULL,

maintenance_date ?????????????????DATE NOT NULL,

details ??????????????????????????VARCHAR2(200),

teslavehicles_vehicle_id ?????????NUMBER NOT NULL,

-- ?ERROR: Column name length exceeds maximum allowed length(30)

service_centers_service_center_id NUMBER NOT NULL

);

ALTER TABLE maintenance_records ADD CONSTRAINT maintenance_records_pk PRIMARY KEY ( maintenance_record_id );

CREATE TABLE part_inventories (

inventory_id ???????NUMBER NOT NULL,

quantity ???????????VARCHAR2(50) NOT NULL,

location ???????????VARCHAR2(50),

parts_part_id ??????NUMBER NOT NULL,

suppliers_suppliers NUMBER NOT NULL

);

ALTER TABLE part_inventories ADD CONSTRAINT part_inventories_pk PRIMARY KEY ( inventory_id );

CREATE TABLE parts (

part_id ??????????????????????????????????NUMBER NOT NULL,

parts_name ???????????????????????????????VARCHAR2(50) NOT NULL,

parts_type ???????????????????????????????VARCHAR2(50) NOT NULL,

price ????????????????????????????????????NUMBER(2, 2) NOT NULL,

-- ?ERROR: Column name length exceeds maximum allowed length(30)

maintenance_records_maintenance_record_id NUMBER

);

ALTER TABLE parts ADD CONSTRAINT parts_pk PRIMARY KEY ( part_id );

CREATE TABLE production (

production_id ???????????NUMBER(1) NOT NULL,

start_date ??????????????DATE NOT NULL,

end_date ????????????????DATE NOT NULL,

status ??????????????????VARCHAR2(50) NOT NULL,

teslavehicles_vehicle_id NUMBER NOT NULL

);

CREATE UNIQUE INDEX production__idx ON

production (

teslavehicles_vehicle_id

ASC );

ALTER TABLE production ADD CONSTRAINT production_pk PRIMARY KEY ( production_id );

CREATE TABLE service_centers (

service_center_id ?????NUMBER NOT NULL,

service_name ??????????VARCHAR2(20) NOT NULL,

address ???????????????VARCHAR2(30),

service_contact_number VARCHAR2(30) NOT NULL

);

ALTER TABLE service_centers ADD CONSTRAINT service_centers_pk PRIMARY KEY ( service_center_id );

CREATE TABLE suppliers (

suppliers ?????NUMBER NOT NULL,

suppliername ??VARCHAR2(50) NOT NULL,

address ???????VARCHAR2(50),

contact_number VARCHAR2(20) NOT NULL,

main_products ?VARCHAR2(50) NOT NULL

);

ALTER TABLE suppliers ADD CONSTRAINT suppliers_pk PRIMARY KEY ( suppliers );

CREATE TABLE teslavehicles (

vehicle_id ??????????????NUMBER NOT NULL,

vehiclesmodel ???????????VARCHAR2

-- ?ERROR: VARCHAR2 size not specified

NOT NULL,

production_date ?????????DATE NOT NULL,

color ???????????????????VARCHAR2

-- ?ERROR: VARCHAR2 size not specified

,

battery_range ???????????VARCHAR2

-- ?ERROR: VARCHAR2 size not specified

NOT NULL,

bookingorders_order_id ??NUMBER(1, 1),

production_production_id NUMBER(1) NOT NULL

);

CREATE UNIQUE INDEX teslavehicles__idx ON

teslavehicles (

production_production_id

ASC );

ALTER TABLE teslavehicles ADD CONSTRAINT teslavehicles_pk PRIMARY KEY ( vehicle_id );

ALTER TABLE bookingorders

ADD CONSTRAINT bookingorders_customers_fk FOREIGN KEY ( customers_customer_id )

REFERENCES customers ( customer_id );

-- ?ERROR: FK name length exceeds maximum allowed length(30)

ALTER TABLE maintenance_records

ADD CONSTRAINT maintenance_records_service_centers_fk FOREIGN KEY ( service_centers_service_center_id )

REFERENCES service_centers ( service_center_id );

-- ?ERROR: FK name length exceeds maximum allowed length(30)

ALTER TABLE maintenance_records

ADD CONSTRAINT maintenance_records_teslavehicles_fk FOREIGN KEY ( teslavehicles_vehicle_id )

REFERENCES teslavehicles ( vehicle_id );

ALTER TABLE part_inventories

ADD CONSTRAINT part_inventories_parts_fk FOREIGN KEY ( parts_part_id )

REFERENCES parts ( part_id );

ALTER TABLE part_inventories

ADD CONSTRAINT part_inventories_suppliers_fk FOREIGN KEY ( suppliers_suppliers )

REFERENCES suppliers ( suppliers );

ALTER TABLE parts

ADD CONSTRAINT parts_maintenance_records_fk FOREIGN KEY ( maintenance_records_maintenance_record_id )

REFERENCES maintenance_records ( maintenance_record_id );

ALTER TABLE production

ADD CONSTRAINT production_teslavehicles_fk FOREIGN KEY ( teslavehicles_vehicle_id )

REFERENCES teslavehicles ( vehicle_id );

ALTER TABLE teslavehicles

ADD CONSTRAINT teslavehicles_bookingorders_fk FOREIGN KEY ( bookingorders_order_id )

REFERENCES bookingorders ( order_id );

ALTER TABLE teslavehicles

ADD CONSTRAINT teslavehicles_production_fk FOREIGN KEY ( production_production_id )

REFERENCES production ( production_id );

-- Oracle SQL Developer Data Modeler Summary Report:

--

-- CREATE TABLE ????????????????????????????9

-- CREATE INDEX ????????????????????????????2

-- ALTER TABLE ????????????????????????????18

-- CREATE VIEW ?????????????????????????????0

-- ALTER VIEW ??????????????????????????????0

-- CREATE PACKAGE ??????????????????????????0

-- CREATE PACKAGE BODY ?????????????????????0

-- CREATE PROCEDURE ????????????????????????0

-- CREATE FUNCTION ?????????????????????????0

-- CREATE TRIGGER ??????????????????????????0

-- ALTER TRIGGER ???????????????????????????0

-- CREATE COLLECTION TYPE ??????????????????0

-- CREATE STRUCTURED TYPE ??????????????????0

-- CREATE STRUCTURED TYPE BODY ?????????????0

-- CREATE CLUSTER ??????????????????????????0

-- CREATE CONTEXT ??????????????????????????0

-- CREATE DATABASE ?????????????????????????0

-- CREATE DIMENSION ????????????????????????0

-- CREATE DIRECTORY ????????????????????????0

-- CREATE DISK GROUP ???????????????????????0

-- CREATE ROLE ?????????????????????????????0

-- CREATE ROLLBACK SEGMENT ?????????????????0

-- CREATE SEQUENCE ?????????????????????????0

-- CREATE MATERIALIZED VIEW ????????????????0

-- CREATE MATERIALIZED VIEW LOG ????????????0

-- CREATE SYNONYM ??????????????????????????0

-- CREATE TABLESPACE ???????????????????????0

-- CREATE USER ?????????????????????????????0

--

-- DROP TABLESPACE ?????????????????????????0

-- DROP DATABASE ???????????????????????????0

--

-- REDACTION POLICY ????????????????????????0

--

-- ORDS DROP SCHEMA ????????????????????????0

-- ORDS ENABLE SCHEMA ??????????????????????0

-- ORDS ENABLE OBJECT ??????????????????????0

--

-- ERRORS ??????????????????????????????????0

-- WARNINGS ????????????????????????????????0