Download the file “homework2 template.sql” from Moodle. Open this file in SQL Se

Question

Download the file “homework2 template.sql” from Moodle. Open this file in SQL Server Management Studio and save it as “homework2.sql” This is the file that I want you to write your SQL code in that you will submit. Create 5 separate databases. Each database should relate to some topic of your choice (e.g., vehicles, restaurants, hobbies, whatever). For EACH database, create at least 3 tables. Each table should be related to the topic for that database. For EACH table, define at least 3 attributes. Overall, you must use each of the following data types at least once (varchar, char, int, date, float, text) For EACH table, insert at least 3 rows of data.

Explanation / Answer

1. SQL for database 'vehicles':

-------------------------------------------

CREATE DATABASE `vehicles`

--
-- Table structure for table `make_details`
--

CREATE TABLE `make_details` (
`make_id` int(11) NOT NULL,
`make_name` tinytext,
`make_location` tinytext,
PRIMARY KEY (`make_id`)
);

--
-- Inserting data for table `make_details`
--

INSERT INTO `make_details` VALUES (1,'General Motors','USA'),(2,'Ford Motors','USA'),(3,'Fiat','Italy');

--
-- Table structure for table `vehicle_details`
--

CREATE TABLE `vehicle_details` (
`registration_no` varchar(10) NOT NULL,
`manufacturing_date` date DEFAULT NULL,
`chasis_no` varchar(10) DEFAULT NULL,
PRIMARY KEY (`registration_no`),
KEY `chasis_no_idx` (`chasis_no`),
CONSTRAINT `chasis_no` FOREIGN KEY (`chasis_no`) REFERENCES `vehilcle_master` (`chasis_no`) ON DELETE NO ACTION ON UPDATE NO ACTION
);

--
-- Inserting data for table `vehicle_details`
--

INSERT INTO `vehicle_details` VALUES ('C1234','2015-12-31','C123'),('M2468','2015-01-10','B123'),('Z1234','2016-01-01','A123');

--
-- Table structure for table `vehilcle_master`
--

CREATE TABLE `vehilcle_master` (
`chasis_no` varchar(20) NOT NULL,
`vehilcle_name` tinytext,
`vehilcle_make` int(11) DEFAULT NULL,
PRIMARY KEY (`chasis_no`),
KEY `make_id_idx` (`vehilcle_make`),
CONSTRAINT `make_id` FOREIGN KEY (`vehilcle_make`) REFERENCES `make_details` (`make_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
);

--
-- Inserting data for table `vehilcle_master`
--

INSERT INTO `vehilcle_master` VALUES ('A123','Beat',1),('B123','Figo',2),('C123','Mini',3);

-------------------------------------

2. SQL for database 'reataurants':

---------------------------------------

CREATE DATABASE `restaurants`

--
-- Table structure for table `menu_details`
--

CREATE TABLE `menu_details` (
`menu_id` int(11) NOT NULL,
`menu_type` char(1) DEFAULT NULL,
`restaurant_id` int(11) DEFAULT NULL,
PRIMARY KEY (`menu_id`),
KEY `restaurant_id_idx` (`restaurant_id`),
CONSTRAINT `restaurant_id` FOREIGN KEY (`restaurant_id`) REFERENCES `restaurant_master` (`restaurant_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
);

--
-- Inserting data for table `menu_details`
--

INSERT INTO `menu_details` VALUES (11,'N',1),(12,'V',2),(13,'N',3);

--
-- Table structure for table `owner_details`
--

CREATE TABLE `owner_details` (
`owner_id` varchar(20) NOT NULL,
`owner_name` varchar(20) DEFAULT NULL,
`owner_location` tinytext,
PRIMARY KEY (`owner_id`)
);

--
-- Inserting data for table `owner_details`
--

INSERT INTO `owner_details` VALUES ('ABC1','Jimmy foods','USA'),('ABC2','Jubli foodworks','Italy'),('ABC3','Mega chain','France');

--
-- Table structure for table `restaurant_master`
--

CREATE TABLE `restaurant_master` (
`restaurant_id` int(11) NOT NULL,
`restaurant_name` varchar(20) DEFAULT NULL,
`owner_id` varchar(20) DEFAULT NULL,
PRIMARY KEY (`restaurant_id`),
KEY `owner_id_idx` (`owner_id`),
CONSTRAINT `owner_id` FOREIGN KEY (`owner_id`) REFERENCES `owner_details` (`owner_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ;

--
-- Inserting data for table `restaurant_master`
--

INSERT INTO `restaurant_master` VALUES (1,'Tops Cafe','ABC1'),(2,'Burger Joint','ABC2'),(3,'Food Junction','ABC3');

-----------------------------------------------------

Note: Similarly, you can create other 3 databases of your choice.

Related Questions

Navigate

• Browse (All)
• Browse D
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.