nfJKVKdJ

1USE MASTER
2GO
3
4DROP DATABASE IF EXISTS GuitarGearDataBase
5GO
6
7CREATE DATABASE GuitarGearDataBase ON PRIMARY(
8	NAME='GuitarGearDataBase',
9	FILENAME='C:\PersonalProject\GuitarGearDataBase.mdf',
10	SIZE=4096KB,
11	FILEGROWTH=1024KB)
12	LOG ON(
13		NAME='Robotics_log',
14		FILENAME='C:\PersonalProject\GuitarGearDataBase.mdf',
15		SIZE=1024KB,
16		FILEGROWTH=10%)
17GO
18
19USE GuitarGearDataBase
20
21CREATE TABLE Guitar(
22	ID 				INT NOT NULL IDENTITY(1, 1),
23	Year			INT NOT NULL,
24	Manufacturer	VARCHAR(255) NOT NULL,
25	Model			VARCHAR(255) NOT NULL,
26	Series			VARCHAR(255) NOT NULL,
27	CONSTRAINT GUITAR_PK PRIMARY KEY(Year, Manufacturer, Model, Series));
28
29CREATE TABLE Body(
30	Guitar_ID 		INT NOT NULL,
31	Body_ID 		INT NOT NULL IDENTITY(1, 1),
32	Material		VARCHAR(255) NOT NULL,
33	Finish			VARCHAR(255) NOT NULL,
34	Shape			VARCHAR(255) NOT NULL,
35	CONSTRAINT BODY_PK PRIMARY KEY(Guitar_ID, Body_ID),
36	CONSTRAINT BODY_GUITAR_FK FOREIGN KEY Guitar_ID REFERENCES Guitar(ID));
37
38CREATE TABLE Neck(
39	Guitar_ID 		INT NOT NULL,
40	Neck_ID			INT NOT NULL IDENTITY(1, 1),
41	Material		VARCHAR(255) NOT NULL,
42	Finish			VARCHAR(255) NOT NULL,
43	Shape			VARCHAR(255) NOT NULL,
44	Scale_Length	VARCHAR(255) NOT NULL,
45	FingerB_Mat		VARCHAR(255) NOT NULL,
46	FingerB_Rad		VARCHAR(255) NOT NULL,
47	Number_Frets	VARCHAR(255) NOT NULL,
48	Nut_Material	VARCHAR(255) NOT NULL,
49	Nut_Width		VARCHAR(255) NOT NULL,
50	End_Board_Wid	VARCHAR(255) NOT NULL,
51	Position_Inlay	VARCHAR(255) NOT NULL,
52	Truss_Rod		VARCHAR(255),
53	Truss_Rod_Nut	VARCHAR(255),
54	CONSTRAINT NECK_PK PRIMARY KEY(Guitar_ID, Neck_ID),
55	CONSTRAINT NECK_GUITAR_FK FOREIGN KEY Guitar_ID REFERENCES Guitar(ID));
56
57CREATE TABLE Hardware(
58	Guitar_ID 		INT NOT NULL,
59	Hardware_ID		INT NOT NULL IDENTITY(1, 1),
60	Bridge			VARCHAR(255) NOT NULL,
61	Tailpiece		VARCHAR(255),
62	Finish			VARCHAR(255) NOT NULL,
63	Tuning_Machine	VARCHAR(255) NOT NULL,
64	Pickguard		VARCHAR(255),
65	Control_Knobs	VARCHAR(255),
66	Switch_Tip		VARCHAR(255),
67	Switch_Washer	VARCHAR(255),
68	Neck_Plate		VARCHAR(255),
69	Jack_Plate		VARCHAR(255),
70	CONSTRAINT HARDWARE_PK PRIMARY KEY(Guitar_ID, Neck_ID),
71	CONSTRAINT HARDWARE_GUITAR_FK FOREIGN KEY Guitar_ID REFERENCES Guitar(ID));
72	
73CREATE TABLE Electronics(
74	Guitar_ID 		INT NOT NULL,
75	Electronics_ID	INT NOT NULL IDENTITY(1, 1),
76	Bridge_Pickup	VARCHAR(255),
77	Middle_Pickup	VARCHAR(255),
78	Neck_Pickup		VARCHAR(255),
79	Controls 		VARCHAR(255),
80	Switching		VARCHAR(255),
81	Configuration	VARCHAR(255) NOT NULL,
82	CONSTRAINT ELECTRONICS_PK PRIMARY KEY(Guitar_ID, Electronics_ID),
83	CONSTRAINT ELECTRONICS_GUITAR_FK FOREIGN KEY Guitar_ID REFERENCES Guitar(ID));
84
85/*CREATE TABLE Amplifier(
86	ID 				INT NOT NULL IDENTITY(1, 1),
87	Year 			INT NOT NULL,
88	Manufacturer	VARCHAR(255) NOT NULL,
89	Model			VARCHAR(255) NOT NULL,
90	Type 			VARCHAR CHECK(Type IN ('Solid-State', 'Tube', 'Hybrid'))
91	Channels		INT NOT NULL, 
92	Wattage			VARCHAR(255) NOT NULL,
93	)*/
94
95CREATE INDEX GUITAR_INDX ON Guitar(Year, Manufacturer, Model);
96CREATE INDEX BODY_INDX ON Body(Material, Finish, Shape);
97CREATE INDEX NECK_MATERIAL_INDX ON Neck(Material, Finish, FingerB_Mat, Nut_Material, Position_Inlay);
98CREATE INDEX NECK_SPEC_INDX ON Neck(Shape, Scale_Length, FingerB_Rad, Number_Frets, Nut_Width, End_Board_Wid, Truss_Rod, Truss_Rod_Nut);
99CREATE INDEX HARDWARE_TYPE_INDX ON Hardware(Bridge, Tailpiece, Tuning_Machine);
100CREATE INDEX HARDWARE_FINISH_INDX ON Hardware(Finish, Pickguard, Control_Knobs, Switch_Tip, Switch_Washer, Neck_Plate, Jack_Plate);
101CREATE INDEX ELECTRONICS_PICKUPS_INDX ON Electronics(Bridge_Pickup, Middle_Pickup, Neck_Pickup);
102CREATE INDEX ELECTRONICS_CONFIGURATION_INDX ON Electronics(Controls, Switching, Configuration);