Part A. Database Creation Design and create a relational database containing inf
ID: 3578073 • Letter: P
Question
Part A. Database Creation
Design and create a relational database containing information on a collection of items, field of study, hobby, or special interest you may have. Your database should have a minimum of 3 tables and include both one-to-many and many-to-many relationships. Be sure to include some numeric and/or date fields. Define all appropriate constraints, using the proper naming conventions (see Structure Notes below).
Populate your database with at least 25 records in the main table(s), and whatever is needed in related tables.
Submit the following:
a short description of the purpose of the database and what the data & relationships mean
an Entity Relationship Logical Data Model of your data
the DDL used to create our database
Part B. Queries
Create queries that include the following. You may use more than one of the listed conditions in the same query.
Retrieve subset of columns.
Uses a simple condition.
Uses a compound condition.
Uses the LIKE, IN, or BETWEEN operator.
Uses an aggregate function.
Uses the GROUP BY clause.
Uses joins to retrieve data from more than one table.
Use the IN or EXISTS operator.
Use a subquery.
Performs an inner or outer join.
Uses ALL or ANY operators.
For each query, submit the following:
Question the query is trying to answer (e.g. similar to the questions in your book like 'What is the price of the most expensive item?')
The SQL for the query that answer the question
The query results.
STRUCTURE NOTES:
Use the proper naming convention for your constraints: Example: Constraint TableName_FieldName_ConstraintID (Customer_CusNum_PK)
Set up the Primary Keys for each table with Constraints listed.
Add Your Foreign Keys for each table with Constraints listed.
Make the Data Types for all the Primary Keys and their corresponding Foreign Keys Varchar(4).
Explanation / Answer
PART A:
Here we will create 3 tables collection_of_items, field_of_study, hobby.
create table collection_of_items
(
ItemId int,
ItemName varchar(255),
ItemDescripton varchar(255),
ItemValue int,
ItemCode int,
ItemQuantity int,
PurchaseDate date,
PRIMARY KEY (ItemCode )
)
create table field_of_study
(
fId int,
FieldName varchar(255),
ItemCode int,
PRIMARY KEY (fId),
FOREIGN KEY (ItemCode) REFERENCES collection_of_items(ItemCode)
);
create table hobby
(
hId int,
hName varchar (255),
fId int,
PRIMARY KEY (hId),
FOREIGN KEY (fId) REFERENCES field_of_study(fId)
);
==============================================
You can insert values into tables using
INSERT INTO table_name (column1,column2,column3,...)
VALUES (value1,value2,value3,...);
==============================================
INSERT INTO collection_of_items values(1,"Music Volume","Contains the genere of music",12344,10000,10,"12/12/2015");
INSERT INTO field_of_study values(1,"Music", 10000);
INSERT INTO hobby VALUES(1,"Singing",1);
you can also use references to add values as tables are in foreign key relationship.
PART B:
some queries are:
Select * from collection_of_items where ItemQuantity>10; // populates all the fiels where ItemQuantity is greater than 10
compound condition :
SELECT * collection_of_items WHERE ItemQuantity> 1 AND ItemQuantity< 10;
Aggregate function:
Groupby/orderby/joins inner.
SELECT collection_of_items.ItemName,COUNT(collection_of_items.ItemQuantity) AS TotalQuantity FROM collection_of_items
LEFT JOIN field_of_study
ON collection_of_items.ItemCode=field_of_study.ItemCode
GROUP BY collection_of_items.ItemName;
the above query is an example of multi functionality query.