Brooklyn Campus Library - LIU, LLC x Ogbonnayajrakpara, LLC

Brooklyn College Ogbonnaya Akpara CIS 7510X HW#6 Professor Alex S. Designing a Junk Yard 1- page description: Customer comes in to check cheap old cars. Customer calls many junk yards for car availability and pricing. Customer saves money to purchase the property. Customer is willing to negotiate with many junk yards on price. Customer verifies that the junk yard is established. Customer confirms that the junk yard is licensed. Customer comes in with old clothes because you are likely to get dirty. Customer packs many kinds of tools. Customer is responsible for removing the part of the vehicle that he or she needs from the abandoned vehicle. Customer stays clear of bio-hazard vehicles because they are marked with bio-hazard caution sign. Customer brings along a friend to help remove the part of the vehicle they want especially if the item he or she wants is large or difficult to remove. Customer purchases a business license from a government agency. Customer takes the precaution to protect its auto junk yard property before stocking it. Example includes fencing and camera. Customer locates and purchases an appropriate property so the junk yard can cover several acres, and must be in a commercially-zoned area. Customer develops a tracking system for their inventory. Customer stocks up on inventory by classified ads, auctions, and estate sales. Customer advertises its auto junk yard by placing ads on internet classified websites and creating a website that allows Internet users to search the customers inventory. Creating table statements: create table Make ( MakeID int not null identity(1,1), Name varchar(50) not null, constraint PK_Make primary key (MakeID) ) create table Model ( ModelID int not null identity(1,1), MakeID int not null, Name varchar(50) not null, constraint PK_Model primary key (ModelID), constraint FK_Model_Make foreign key (MakeID) references Make(MakeID) ) create table [Year] ( YearID tinyint not null identity(1,1), Name char(4) not null, constraint PK_Year primary key (YearID) ) create table Model2Year ( Model2YearID int not null identity(1,1), ModelID int not null, YearID tinyint not null, constraint PK_Model2Year primary key (Model2YearID), constraint FK_Model2Year_Model foreign key (ModelID) references Model(ModelID), constraint FK_Model2Year_Year foreign key (YearID) references [Year](YearID) ) create table Category ( CategoryID tinyint not null identity(1,1), Name varchar(30) not null, constraint PK_Category primary key (CategoryID) ) create table Subcategory ( SubcategoryID tinyint not null identity(1,1), CategoryID tinyint not null, Name varchar(30) not null, constraint PK_Subcategory primary key (SubcategoryID), constraint FK_Subcategory_Category foreign key (SubcategoryID) references Category(CategoryID) ) create table Model2Year2Category ( Model2Year2CategoryID int not null identity(1,1), Model2YearID int not null, CategoryID tinyint not null, constraint PK_Model2Year2Category primary key (Model2Year2CategoryID), constraint FK_Model2Year2Category_Model2Year foreign key (Model2YearID) references Model2Year(Model2YearID), constraint FK_Model2Year2Category_Category foreign key (CategoryID) references Category(CategoryID) ) create table Brand ( BrandID int not null identity(1,1), Name varchar(50) not null, constraint PK_Brand primary key (BrandID) ) create table Brand2Subcategory ( Brand2SubcategoryID int not null identity(1,1), BrandID int not null, SubcategoryID int not null, constraint PK_Brand2Subcategory primary key (Brand2SubcategoryID), constraint FK_Brand2Subcategory_Brand foreign key (BrandID) references Brand(BrandID), constraint FK_Brand2Subcategory_Subcategory foreign key (SubcategoryID) references Subcategory(SubcategoryID) ) create table Part ( PartID int not null identity(1,1), Name varchar(50) not null, BrandID int not null, PartNumber varchar(50) not null, Description varchar(200), constraint PK_Part primary key (PartID), constraint FK_Part_Brand foreign key (BrandID) references Brand(BrandID) ) create table Model2Year2Part ( Model2Year2PartID int not null identity(1,1), Model2YearID int not null, PartID int not null, constraint PK_Model2Year2Part primary key (Model2Year2PartID), constraint FK_Model2Year2Part_Model2Year foreign key (Model2YearID) references Model2Year(Model2YearID), constraint FK_Model2Year2Part_Part foreign key (PartID) references Part(PartID) ) create table Brand2Subcategory2Part ( Brand2Subcategory2PartID int not null identity(1,1), Brand2SubcategoryID int not null, PartID int not null, constraint PK_Brand2Subcategory2Part primary key (Brand2Subcategory2PartID), constraint FK_Brand2Subcategory2Part_Brand2Subcategory foreign key (Brand2SubcategoryID) references Brand2Subcategory(Brand2SubcategoryID), constraint FK_Brand2Subcategory2Part_Part foreign key (PartID) Query Statements: 1) select makeID, identity(1,1), fname, lname from make where fname is not null, lname is not null, identity(1,1) is not null && makeID is not null 2) select modelID, makeID, identity(1,1), fname, lname from model where fname is not null, lname is not null, identity(1,1) is not null, makeID is not null, && modelID 3) select yearID, identity(1,1), fname, lname from year where fname is not null, lname is not null, identity(1,1) is not null && yearID is not null 4) select model2yearID, modelID, yearID, identity(1,1) from model2year where model2yearID is not null, identity(1,1) is not null, modelID is not null, && yearID is not null 5) select categoryID, identity(1,1), fname, lname from category where fname is not null, lname is not null, identity(1,1) is not null && categoryID is not null 6) select sucategoryID, categoryID, identity(1,1), fname, lname from subcategory where fname is not null, lname is not null, identity(1,1) is not null, categoryID is not null, subcategoryID is not null 7) select model2year2categoryID, model2yearID, categoryID, identity(1,1) from model2yearcategory where identity(1,1) is not null, model2year2categoryID is not null, categoryID is not null, && model2yearID is not null 8) select brandID, identity(1,1), fname, lname from brand where fname is not null, lname is not null, identity(1,1) is not null && brandID is not null 9) select brandID, identity(1,1), subcategoryID, brand2subcategoryID from brand2subcategory where brandID is not null, identity(1,1) is not null, brand2subcategoryID is not null, && subcategoryID is not null 10) select partID, identity(1,1), partNumber, fname, lname, brandID, description from part where fname is not null, lname is not null, identity(1,1) is not null, brandID is not null, description is not null, partNumber is not null && partID is not null 11) select model2year2partID, identity(1,1), partID, model2yearID from model2year2part where identity(1,1) is not null, partID is not null, model2yearID is not null && model2year2partID is not null 12) select brand2subcategory2partID, partID, identity(1,1), brand2subcategoryID from brand2subcategory2part where identity(1,1) is not null, partID is not null, brand2subcategory2partID is not null && brand2subcategoryID is not null
Database-hw6 by OgboJr Productions, LLC | Request PDF. Available from: https://www.researchgate.net/publication/329642265_Database-hw6_by_OgboJr_Productions_LLC [accessed Dec 16 2018].