· 8 years ago · Nov 15, 2017, 05:10 PM
1USE MASTER
2
3GO
4if exists (select * from sysdatabases where name='MM')
5DROP DATABASE MM;
6GO
7CREATE DATABASE MM;
8go
9USE MM
10
11CREATE TABLE Customer (
12 customer_numb INT,
13 customer_name VARCHAR(40),
14 customer_street VARCHAR(40),
15 customer_city VARCHAR(40),
16 customer_state VARCHAR(40),
17 customer_zip CHAR(5),
18 contact_person VARCHAR(40),
19 contact_phone CHAR(12),
20 contact_fax CHAR(10)
21 PRIMARY KEY (customer_numb));
22
23CREATE TABLE Product_status (
24 status_code INTEGER,
25 status_description VARCHAR(40),
26 PRIMARY KEY (status_code));
27
28CREATE TABLE Feature (
29 feature_code INTEGER,
30 feature_description VARCHAR(40),
31 PRIMARY KEY (feature_code));
32
33CREATE TABLE Model(
34model_numb INT not null,
35model_description VARCHAR(40) NOT NULL,
36suggested_retail_price MONEY NOT NULL,
37shipping_weight FLOAT NOT NULL,
38time_to_manufacture TIME NOT NULL,
39PRIMARY KEY (model_numb)
40);
41
42CREATE TABLE Learn_about(
43learn_code INT NOT NULL,
44learn_description VARCHAR(40) NOT NULL,
45PRIMARY KEY (learn_code)
46);
47
48CREATE TABLE Test_type (
49test_code INT,
50test_decription VARCHAR(40),
51PRIMARY KEY (test_code));
52
53CREATE TABLE [Owner](
54owner_numb INT,
55owner_first_name VARCHAR(40),
56owner_last_name VARCHAR(40),
57owner_street VARCHAR(40),
58owner_city VARCHAR(40),
59owner_state CHAR(2),
60owner_zip CHAR(5),
61owner_phone VARCHAR(13)
62PRIMARY KEY(owner_numb));
63
64CREATE TABLE Manufacturing_line(
65line_numb INT,
66line_status VARCHAR(40),
67PRIMARY KEY(line_numb));
68
69CREATE TABLE Raw_material(
70material_id_numb INTEGER,
71material_name CHAR(30),
72unit_of_measurement VARCHAR(40),
73quantity_in_stock INTEGER,
74reorder_point INTEGER,
75PRIMARY KEY (material_id_numb)
76);
77
78CREATE TABLE Supplier (
79supplier_numb INTEGER,
80supplier_name CHAR(40),
81supplier_street CHAR(50),
82supplier_city CHAR(20),
83supplier_state CHAR(2),
84supplier_zip CHAR(10),
85supplier_contact CHAR(15),
86suppler_phone CHAR(12),
87PRIMARY KEY (supplier_numb));
88
89CREATE TABLE Problem_type
90(
91problem_type_code INT NOT NULL,
92problem_type_description VARCHAR(40),
93PRIMARY KEY (problem_type_code));
94
95CREATE TABLE Material_order (
96 po_numb INT,
97 supplier_numb INT,
98 material_order_date DATE,
99 material_order_total INT,
100 PRIMARY KEY(po_numb),
101 FOREIGN KEY(supplier_numb) REFERENCES Supplier
102 );
103
104CREATE TABLE [Order] (
105 order_numb INT,
106 customer_numb INT,
107 order_date DATE,
108 order_total MONEY,
109 order_filled BIT,
110 PRIMARY KEY(order_numb),
111 FOREIGN KEY(customer_numb) REFERENCES Customer
112 );
113
114CREATE TABLE Material_needed (
115 model_numb INT,
116 material_id_num INT,
117 quantity_needed INT,
118 PRIMARY KEY(model_numb, material_id_num),
119 FOREIGN KEY(model_numb) REFERENCES Model,
120 FOREIGN KEY(material_id_num) REFERENCES Raw_material
121 );
122
123CREATE TABLE Material_order_line (
124po_numb INTEGER NOT NULL,
125material_id_numb INTEGER NOT NULL,
126material_quantity INTEGER NOT NULL,
127material_cost_each MONEY NOT NULL,
128material_line_cost MONEY NOT NULL,
129PRIMARY KEY (po_numb,material_id_numb),
130FOREIGN KEY (po_numb) REFERENCES Material_order(po_numb),
131FOREIGN KEY (material_id_numb) REFERENCES Raw_material(material_id_numb));
132
133CREATE TABLE Line_schedule (
134line_numb INTEGER NOT NULL,
135production_date DATE NOT NULL,
136model_numb INTEGER NOT NULL,
137quantity_to_produce INTEGER NOT NULL,
138PRIMARY KEY (line_numb,production_date),
139FOREIGN KEY (line_numb) REFERENCES Manufacturing_line(line_numb),
140FOREIGN KEY (model_numb) REFERENCES Model(model_numb));
141
142CREATE TABLE Test (
143model_numb INTEGER NOT NULL,
144test_date DATE NOT NULL,
145test_location VARCHAR(40) NOT NULL,
146test_code INTEGER NOT NULL,
147test_results FLOAT NOT NULL,
148PRIMARY KEY (model_numb),
149FOREIGN KEY (model_numb) REFERENCES Model(model_numb),
150FOREIGN KEY (test_code) REFERENCES Test_type(test_code));
151
152CREATE TABLE Order_line (
153 order_numb INT FOREIGN KEY (order_numb) REFERENCES [Order],
154 model_numb INT FOREIGN KEY (model_numb) REFERENCES Model,
155 quantity_ordered INT,
156 unit_price MONEY,
157 line_total MONEY,
158 all_shipped BIT,
159
160 PRIMARY KEY (order_numb , model_numb)
161);
162
163CREATE TABLE Shippment (
164 order_numb INT FOREIGN KEY (order_numb) REFERENCES [Order],
165 model_numb INT FOREIGN KEY (model_numb) REFERENCES Model,
166 shipping_date DATE,
167 quantity_shipped INT,
168
169 PRIMARY KEY (order_numb , model_numb, shipping_date)
170);
171
172CREATE TABLE Product (
173 serial_numb INT PRIMARY KEY,
174 model_numb INT FOREIGN KEY (model_numb) REFERENCES Model,
175 date_manufacturing DATE,
176 status_code INT FOREIGN KEY (status_code) REFERENCES Product_status,
177 date_shipped DATE,
178 order_numb INT FOREIGN KEY (order_numb) REFERENCES [Order],
179);
180
181CREATE TABLE Purchase
182(serial_numb INT NOT NULL,
183owner_numb INT NOT NULL,
184age INT,
185gender CHAR,
186purchase_date DATE,
187purchase_place VARCHAR(40),
188learn_code INT NOT NULL,
189relationship VARCHAR(40),
190CONSTRAINT Purchase_PK PRIMARY KEY(serial_numb, owner_numb),
191CONSTRAINT Purchase_FK1 FOREIGN KEY (serial_numb) REFERENCES Product(serial_numb),
192CONSTRAINT Purchase_FK2 FOREIGN KEY (owner_numb) REFERENCES [Owner](owner_numb),
193CONSTRAINT Purchase_FK3 FOREIGN KEY (learn_code) REFERENCES Learn_about(learn_code));
194
195CREATE TABLE Purchase_feature
196(serial_numb INT NOT NULL,
197feature_code INT NOT NULL,
198CONSTRAINT Purchase_feature_PK PRIMARY KEY (serial_numb, feature_code),
199CONSTRAINT Purchase_feature_FK1 FOREIGN KEY (serial_numb) REFERENCES Product(serial_numb),
200CONSTRAINT Purchase_feature_FK2 FOREIGN KEY (feature_code) REFERENCES Feature(feature_code));
201
202CREATE TABLE Problem_report
203(serial_numb INT NOT NULL,
204problem_date DATE NOT NULL,
205problem_time TIME NOT NULL,
206problem_type_code INT NOT NULL,
207problem_description TEXT,
208CONSTRAINT Problem_report_PK PRIMARY KEY (serial_numb, problem_date),
209CONSTRAINT Problem_report_FK1 FOREIGN KEY (serial_numb) REFERENCES Product(serial_numb),
210CONSTRAINT Problem_report_FK2 FOREIGN KEY (problem_type_code) REFERENCES Problem_type(problem_type_code));