Shoprite is a big shopping mall in the town of Ibadan, Capital of Oyo Talk about Nigeria; it is located at the center of the town, making it easily accessible to customers via any area of the City, Popular for selling good quality products at affordable prices.
Shoprite provides goods of different types and from different brands and sizes which comprises of automobiles, clothing, Household Electronics, Office Electronics, Computer and its own Accessories, gadgets, toiletries, Drinks, Furniture, Cosmetics, Music Instruments and almost everything needed within an average home
Shoprite has over three hundred (300) employees, working in several units of the company; these employees help with keeping the business moving, through ensuring customers are satisfied atlanta divorce attorneys transaction made with the shopping mall.
Business Activities within Shoprite moves in this explanation,
Goods are offered to Shoprite in mass, which are saved in to the INVENTORY BOOK
Payments for the goods offered, if goods are in good shape, payment is manufactured.
Shoprite markets goods offered, to its customers in products.
For every transaction, a worker is dedicated to completing the business deal, which is saved into a book called the SALES Reserve.
Shoprite has discovered that some anomalies are preparing up in the company; like
Goods are tired in a very short time and income made will not match expected income.
Shoprite want a repository system to control the following
Inventory
Customer Details
Supplier
Courier
What Is the Problem?
Inventory Management: Shoprite like any shopping mall is affected by the differing market trends and as such it is a herculean process to properly ascertain the stock and restock levels. For instance the quantity of toys sold in July-August is substantially higher than other months. Currently, the management of product lines is hectic and the warehouse administrator is hard-pressed to find the placing your order times and volumes right.
Queue: Shoprite is faced with long queue at the shopping mall, because sales reps cannot provide correct information about a client's order. Say for example a customer approaches a sales rep and places an order; the sales rep phone calls the store office to check on for the option of the merchandise, the store employee receives the get and passes the submission unto another employee "B" in the store team for information retrieval, Employee "B" bank checks the store for the availability of the order and realizes the merchandise is unavailable, passes the concept to employee A, worker A calling the sales rep and provides the communication to the customer. If this is performed for many customers, time taken up to process an order has always been Slow and Time consuming.
Why is it considered an issue?
Inventory Management: For example the amount of toys sold in July-August is significantly greater than other months. Presently, the management of product lines is busy and the warehouse supervisor is hard-pressed to have the placing your order times and volumes right.
Queue: For instance a customer approaches a sales rep and places an order; the sales consultant phone calls the store team to check for the availability of the merchandise, the store staff receives the request and passes the question unto another worker "B" in the store section for information retrieval, Worker "B" checks the store for the option of the order and finds out the product is unavailable, moves the concept to employee A, employee A phone calls the sales representative and offers the note to the client. If this is performed for many customers, time taken up to process an order is definitely Slow and Frustrating.
What will be the Benefits Resolving it?
The advantages of this job are both non-financial and financial. The benefits associated with developing a relational repository management system (RDBMS) are the following.
Better efficiency: The deployment of an operating relational databases management system will increase staff output because customers will no longer have to queue up for a longer time to buy goods, Ventures will be prompt.
Prompt Transaction: Customers will opt to buy goods from Shoprite because orders with Shoprite are quick.
Good Customer Marriage: Contact information on customers can be stored and retrieved for future recommendations example promos and raffle draws.
Search: Customers will no longer queue up ever again for something that is out of stock, Quite simply, searching for a product will be easy an instant, so you will see no squandering of customer's valuable time.
Staff Strength: You will see no need to Increase staff power to cater for customer request because the relational database management system will ensure that business deal are done effectively and successfully.
Software Builders who might want to build user interface software for the Shoprite need not spend time building a repository, because this will serve most of its data source needs and may even provide as a kind of data requirement
What might the answer look like?
This solution will look just like a complete shopping center repository, which would enable the management staff of Shoprite Supermarket to manage the business effectively and successfully.
What specifically, do you aim to deliver?
I will deliver the next,
A repository schema: That is a description of your database which is a assortment of database objects, such as tables, views, clusters, steps and it is generated using data classification language.
MS SQL Database Scripts: This is Structured Query Dialect which is the terminology relational data source management understands, It'll contain chunks rules for the creation of database objects like Desks, Indexes, Views etc.
Account of Literature
Theory
These are materials to aid data source for a RETAIL CENTER:
NIIT (Microsoft SQL Server 2000 Database Design)
This provided me a good understanding of
What a really database is
How to design a database
Constructing a database
Managing a database
Managing MSSQL Server
The following Topics are extremely useful in the development of the data source and its own objects
Implementing Indexes: Indexes enhance the performance of concerns, they speed up data retrieval, looked after enforces the uniqueness of rows, Indexes is an internal table structure that relational databases use to provide fast access to the rows of a table based on the values of 1 or even more columns, they are likened to the index of your book. A couple of two types of indexes NONCLUSTERED or CLUSTERED
NONCLUSTERED: Nonclustered indexes are typically created on columns used in joins and where clauses and whose worth are revised frequently.
CLUSTERED: Data is actually sorted and only 1 clustered index can be created per desk, so that it should be created on columns that have a high percentage of uniqueness ideals and that are not improved often.
An index can be creating using the syntax below
CREATE NONCLUSTERED INDEX idxNameofindex ON tablename (columnname)
CREATE CLUSTERED INDEX idxNameofindex ON tablename (columnname)
Where
Create is the keyword to create indexes
IdxNameofindex specifies the name of the index
Columnname is the column where index will be created
CLUSTERED or NONCLUSTERED specifies the kind of index.
Creating Views: View are virtual tables which give access to a subset of columns in one or more desks, views are query stored as an thing in the databases, views retrieve its data in one or more desks called bottom part or underlying dining tables,
Views help simplify questions,
Views are also a security mechanism; users can only just retrieve and alter only the info seen by them, thus not impacting the underlying desk(s)
View can be made out of the syntax below
CREATE VIEW vwNameOfView
AS
Select statement
Where
Create is the keyword to generate Views
vwNameOfView specifies the name of the View
select assertion specifies the query
Implementing Stored Procedures: Stored procedures are collection of T-SQL assertions or control-of-flow vocabulary that is stored under one name and carried out as an individual unit.
Stored procedure assists with the following
Improved Performance of queries
Reduction in network congestion
CREATE Process proc_name
AS
BEGIN
Sql_statement1
Sql_declaration2
END
Where
Create is the keyword to build Stored Procedures
proc_name specifies the name of the Stored Procedures
BEGIN commences the statements
END ends the statements
Creating Triggers: Triggers are a block of code that takes its set of T-Sql Statement that is activated in response to certain activities. They are simply like special stored types of procedures that are triggered in response to a meeting, such as changes takes place. Lead to are always described on a stand and is also fired whenever Data Manipulation Dialect (DML) statements like INSERT, Upgrade or DELETE carried out, Triggers assist in maintaining consistency, reliable and appropriate data in dining tables.
CREATE Cause triggername
ON tablename
FOR (Add|Revise|DELETE)
AS
Sql_Statement
Create is the keyword to generate Triggers.
Triggername specifies the name of the trigger
tablename specifies the name of the trigger
This helped a great deal about how to assemble requirements, how to execute interviews, offering questionnaires to staff, naturalistic observation etc.
Methods
Creating Views:
CREATE VIEW dbo. VIEW1
AS
SELECT dbo. orders. OrderDate, dbo. orders. ProductID, dbo. orders. EmployeeID, dbo. orders. OrderID, dbo. requests. CustomerID, dbo. products. ProductID AS Expr1,
dbo. products. ProductName, dbo. products. SupplierID, dbo. products. CatID, dbo. shippers. ShipperID, dbo. shippers. CompanyName,
dbo. shippers. ContactPerson,
dbo. shippers. CompanyAddress, dbo. shippers. State, dbo. shippers. City, orders_1. OrderID AS Expr2, purchases_1. CustomerID AS Expr3,
orders_1. EmployeeID AS Expr4,
orders_1. ProductID AS Expr5
FROM dbo. orders INNER JOIN
dbo. products ON dbo. purchases. ProductID = dbo. products. ProductID INNER JOIN
dbo. orders orders_1 ON dbo. products. ProductID = orders_1. ProductID INNER JOIN
dbo. shippers ON requests_1. ShipperID = dbo. shippers. ShipperID
Implementing Stored Techniques: Stored methods are assortment of T-SQL claims or control-of-flow language that is stored under one name and executed as a single unit.
Stored procedure helps in the following
Improved Performance of queries
Reduction in network congestion
CREATE Technique proc_name
AS
BEGIN
Sql_assertion1
Sql_declaration2
END
Where
Create is the keyword to build Stored Procedures
proc_name specifies the name of the Stored Procedures
BEGIN commences the statements
END ends the statements
Creating Triggers:
CREATE Cause [del] ON [dbo]. [orders]
FOR DELETE
AS
Print 'Deletion Of Requests is not Allowed'
ROLLBACK TRANSACTION
RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Account of Outcome
Analysis
I used interviewing users, so I consulted a pal who works in Shoprite and asked me over. I was able to experience how business exchange occurs in the shopping, documented the process you will need a customer to produce a purchase and exactly how sales representative grant customer's request. I noted a picture where these were complaining about one of the suppliers who have failed to source goods and they lost his/her contact.
I was able to interview a sales rep as well as the Store Keeper, The Administrator of the Shopping mall, asked them about the next.
Description of their daily function: How they do their careers, what must be done, what must be accessible to achieve results?
Exchange of information: The way they exchange information within the mall, how tedious is working with other departments of the mall.
What they dislike about their functions
Based on my interview/research, I could gather the next requirements for Shoprite.
The data requirement of database development for Shoprite is shown below
Product Categories: The data source development should deal with product categories, this part of the development should group products in categories, and everything products must maintain one category.
Customer's details: The database development should control customer details; this part of the development should store customer details, which can be useful in checking sales.
Order details: The database development should manage order details; this portion of the development should control sales made by a particular staff.
Orders: The repository development should manage orders, this part of the development should manage daily sales, the employee accountable for the sale, the customer's id, freight details et al.
Products: The repository development should control product details; this part of the development should control the merchandise and their details including their categories.
Courier services: The databases development should take care of courier partners of Shoprite customer details; this part of the development should store courier details, which pays to in dispatching orders.
Suppliers: The databases development should deal with provider details; this portion of the development should store supplier's details, which is useful in taking care of the inventory.
The development of databases for Shoprite should put into practice the following
Speedy retrieval of results from the databases.
These are virtual furniture which give usage of a subset of columns in one or more dining tables.
The repository should ensure consistency, should be utilized for validation, and really should reject deletion of any order.
The data source should automate sales.
Based on each one of these requirements, I was able to identify the following entity turned furniture with their capabilities (columns)
Categories - Columns
CatID: This specifies the unique identifier for the categories desk.
CatName: This specifies the Category Name that products must belong.
Description: This specifies the description of the category.
Img: this specifies the category image.
Customer - Columns.
customerid: This specifies the initial identifier for the customer table
customername: This specifies the client name.
companyname: This specifies the business name where customer works.
salutation: This specifies customer's salutation.
gender: This specifies the customer's gender.
dob: This specifies the customer's night out of delivery.
address: This specifies the customer's address.
city: This specifies the customer's city.
state: This specifies the customer's point out.
phone: This specifies the customer's phone number.
email: This specifies the customer's email.
Order details - Columns.
odID: This specifies the initial identifier for the order details desk.
EmployeeID: This specifies the Worker Id who was accountable for order.
OrderID: This specifies the order personal information for the order.
ProductID: this specifies the merchandise individuality for the order.
UnitPrice: This specifies the purchase price for each product of product.
Quantity: This specifies the quantity of products sold in each order.
Discount: This specifies the discount on each order.
Orders - Columns.
OrderID: This specifies the unique identifier for the requests table.
CustomerID: This specifies the client who makes purchase.
EmployeeID: This specifies the Worker Id who was simply in charge of a sale.
ProductID: this specifies the product identity for the merchandise purchased.
OrderDate: This specifies the date an order was made.
RequiredDate: This specifies the time an order is necessary by a person. This is dependant on the customer.
ShipperID: This specifies the individuality of the shipper (courier) that is in charge or would be responsible for the delivery of any order.
ShippedDate: This specifies the time an order is shipped to customer's address.
Freight: This specifies the fee moving the order.
Products - Columns.
ProductID: This specifies the unique identifier for the product table.
ProductName: This specifies the name of the merchandise.
SupplierID: This specifies the Supplier Id who offered the product.
CatID: This specifies the category id that the product belongs.
stockPerUnit: This specifies the amount of packs or a large number of a product.
UnitPrice: This specifies the price of a device of something.
UnitsInStock: This specifies the total number of models of something available.
ReorderLevel: This specifies the limit where new stock is bought for the store.
Discontinued: This specifies
Courier services - Columns.
ShipperID: This specifies the initial identifier for the shippers stand.
CompanyName: This specifies the shipper's company name.
ContactPerson: This specifies the name of any contact person working for the courier company (Shipper).
ProductID: this specifies the merchandise identity for the product purchased.
CompanyAddress: This specifies the business's address (Shipper's Address).
City: This specifies the city, where in fact the courier company can be found.
State: This specifies their state where in fact the courier company can be found.
Country: This specifies the country where the courier company is situated.
phone: This specifies the business's contact number.
email: This specifies the company's email.
Suppliers - Columns.
SupplierID: This specifies the initial identifier for the Supplier's stand.
CompanyName: This specifies the Supplier's company name.
ContactName: This specifies the name of the contact person working for the dealer company.
ContactTitle: This specifies the position that the contact person holds.
Address: This specifies the company's address (Supplier's Address).
City: This specifies the town, where the company company is situated.
State: This specifies the state where the provider company can be found.
Zipcode: This specifies the zip code of metropolis the provider company can be found.
Country: This specifies the united states where the company company can be found.
phone: This specifies the business's contact number.
email: This specifies the business's email.
Views: These are virtual furniture which give access to a subset of columns from one or more tables.
Indexes: This boosts query retrieval of any desk in the databases.
Triggers: This means that no order is deleted in the requests table.
Synthesis
After the research, the dining tables were created, here are the syntaxes used to synthesis the requirements
Product Categories: The syntax for the creation of the categories table can be found in the appendices labeled
/****** Subject: Table [dbo]. [categories] ******/
Customer's details:
The syntax for the creation of the Customers table are available in the appendices labeled
/****** Subject: Desk [dbo]. [customers] ******/
Order details
The syntax for the creation of the Order details desk are available in the appendices labeled
/****** Thing: Desk [dbo]. [order_details] ******/
Orders:
The syntax for the creation of the Order table can be found in the appendices labeled
/****** Thing: Table [dbo]. [orders] ******/
Products
The syntax for the creation of the Order stand can be found in the appendices labeled
/****** Thing: Table [dbo]. [orders] ******/
Courier services
The syntax for the creation of the courier table are available in the appendices labeled
/****** Object: Stand [dbo]. [shippers] ******/
Suppliers
The syntax for the creation of the Dealer table can be found in the appendices labeled
/****** Object: Desk [dbo]. [suppliers] ******/
Views
The syntax for the creation of the view desk can be found in the appendices labeled
/****** Object: View dbo. vieworders ******/
Indexes
The syntax for the creation of the Indexes are available in the appendices labeled
/****** Indexes *****/
Triggers
The syntax for the creation of the trigger are available in the appendices labeled
/****** Thing: Lead to dbo. del ******/
Stored Procedures
The syntax for the creation of the Stored Process can be found in the appendices labeled
/****** Subject: Stored Procedure dbo. insertOrders ******/
Evaluation
My evaluation is dependant on the data requirements specified above
Expert Evaluator's Name: Olayode Ezekiel
Session Particular date: 08 01, 2010.
Session Start Time: 12:15 pm.
Session End Time: 3:00 pm.
Task being performed
(if relevant)
Heuristic being assessed
If heuristic is violated, usability defect description
Expert Evaluator's comments or recommendations about the usability defect when the heuristic is violated.
OR
Note here if
the heuristic can't be assessed (justify why it can't be assessed)
OR
the heuristic is not violated when applied (justify why it is not violated)
The databases development should manage product categories
The Databases must take care of product categories.
Heuristic not violated.
A table known as categories has been intended to manage all product categories in the shopping mall. Something must belong to a category; in others words a product must have a CATID.
The data source development should deal with customer details
The Databases must take care of customer details.
Heuristic not violated.
A table called Customer has been created to control all customer details, a unique Id is directed at each customer which is required for any purchase the customer wishes to make in the foreseeable future.
The database development should manage order details
The Repository must take care of Order details.
Heuristic not violated.
A table called requests has been intended to take care of all customer requests, customer unique Identification is required to complete every order.
The databases development should manage product details
The Repository must take care of Products details
Heuristic not violated.
A table known as products has been intended to manage all products and their details; a unique Id is directed at each product, which distinguishes it out of every other product. In addition, it provides as the inventory supervisor, they sale of something decreases the quantity in the merchandise in the product table.
The databases development should control courier lovers of Shoprite customer details
The Databases must deal with Shoprite's courier partners.
Heuristic not violated.
A table named shipper has been created to control all courier lovers.
The data source development should take care of supplier details
The Databases must manage Shoprite's supplier associates.
Heuristic not violated.
A table known as Provider has been created to control all product suppliers, In case a products has reach re-order level, the company is contacted to make source.
Speedy retrieval of results from the repository.
Does this meet Data Need?
The heuristic is not violated.
Indexes has been created for all desks in the databases to ease retrieval of data in the databases
These are digital dining tables which give usage of a subset of columns from one or more dining tables.
Does this meet Data Requirement?
The heuristic is not violated.
Views named vieworders has been intended to display sales.
The data source should ensure consistency, should be used for validation, and really should reject deletion of any order.
Does this meet Data Necessity?
The heuristic is not violated.
A trigger "del" has been intended to ensure steadiness and reject any deletion to the requests tables.
The database should automate sales.
Does this meet Data Need?
The heuristic is not violated.
A Purchase to help in sales has been created.
Review Level of Development
The development of the repository is almost completed, to be expressed in term of percentage; its ratio of completion is approximately 75%.
The requirement of the database is almost fulfilled, the following has been created
Tables
Views
Indexes
Triggers
Constraints
The database is now able to manage
Employees
Products
Orders
Shippers (Courier)
Suppliers
Review Job Management
Getting data need was tedious, so I put in place the Incremental Model; I broke down the necessity in to various functional devices, and implemented as I could accumulate more requirements.
I prioritize the functional products with various activities to be performed in each of them,
Each functional product was implemented within an increment and the ultimate design was achieved
This was achieved by applying the three stages of the increment model
Design: With this phase the most notable most activity was determined and design.
Implementation: After the design it was implemented
Analysis: After implementation the partial functional device was examined.
For every functional unit, these were all exceeded through these stages.
Review of personal development
I individually found the project interesting, made me understand what it takes to develop an it product, from planning to implementation; It was both challenging and interesting.
I had to visit Shoprite interview them to gather necessity, that was a challenge, been to other stores to gain more knowledge
Read catalogs to come solve every problem identified,
The project expanded my understanding of project management, How exactly to Analyze, Synthesize and assess projects,
The task also increase my understanding of in database design
I upgraded in creating ventures and creating causes.
Epilogue
For anyplace I didn't include the creation of your stored treatment,
A stored technique was created called insertorders to automate sales and also reduce the units of something in stock once the transfer is completed.
Appendices
/****** Object: Table [dbo]. [categories] ******/
CREATE TABLE [dbo]. [categories] (
[CatID] [int] Id (1, 1) NOT NULL,
[CatName] [char] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Explanation] [char] (1500) COLLATE SQL_Latin1_Standard_CP1_CI_AS NULL,
[img] [image] NULL
) ON [Major] TEXTIMAGE_ON [Major]
GO
/****** Thing: Stand [dbo]. [customers] ******/
CREATE TABLE [dbo]. [customers] (
[customerid] [int] IDENTITY (1, 1) NOT NULL,
[customername] [varchar] (40) COLLATE SQL_Latin1_Standard_CP1_CI_AS NOT NULL,
[companyname] [varchar] (40) COLLATE SQL_Latin1_Standard_CP1_CI_AS NULL,
[salutation] [char] (10) COLLATE SQL_Latin1_Standard_CP1_CI_AS NULL,
[gender] [varchar] (30) COLLATE SQL_Latin1_Basic_CP1_CI_AS NULL,
[dob] [datetime] NOT NULL,
[address] [varchar] (50) COLLATE SQL_Latin1_Standard_CP1_CI_AS NULL,
[city] [varchar] (50) COLLATE SQL_Latin1_Standard_CP1_CI_AS NULL,
[Talk about] [varchar] (50) COLLATE SQL_Latin1_Standard_CP1_CI_AS NULL,
[telephone] [char] (15) COLLATE SQL_Latin1_Standard_CP1_CI_AS NULL,
[email] [varchar] (50) COLLATE SQL_Latin1_Basic_CP1_CI_AS NULL
) ON [Principal]
GO
/****** Subject: Desk [dbo]. [employees] ******/
CREATE TABLE [dbo]. [employees] (
[EmployeeID] [int] Personal information (1, 1) NOT NULL,
[LastName] [varchar] (60) COLLATE SQL_Latin1_Basic_CP1_CI_AS NOT NULL,
[FirstName] [varchar] (50) COLLATE SQL_Latin1_Basic_CP1_CI_AS NOT NULL,
[Subject] [varchar] (90) COLLATE SQL_Latin1_Standard_CP1_CI_AS NULL,
[TitleOfCourtesy] [varchar] (75) COLLATE SQL_Latin1_Standard_CP1_CI_AS NULL,
[Gender] [varchar] (50) COLLATE SQL_Latin1_Standard_CP1_CI_AS NULL,
[BirthDate] [datetime] NOT NULL,
[HireDate] [datetime] NULL,
[Address] [varchar] (180) COLLATE SQL_Latin1_Standard_CP1_CI_AS NULL,
[City] [varchar] (50) COLLATE SQL_Latin1_Standard_CP1_CI_AS NULL,
[Region] [varchar] (50) COLLATE SQL_Latin1_Basic_CP1_CI_AS NULL,
[PostalCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Country] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Mobile] [char] (72) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Expansion] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[email] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[img] [image] NULL,
[Notes] [text] COLLATE SQL_Latin1_Standard_CP1_CI_AS NULL,
[ReportsTo] [int] NULL
) ON [Major] TEXTIMAGE_ON [Key]
GO
/****** Object: Desk [dbo]. [shippers] ******/
CREATE TABLE [dbo]. [shippers] (
[ShipperID] [int] Personality (1, 1) NOT NULL,
[CompanyName] [varchar] (120) COLLATE SQL_Latin1_Basic_CP1_CI_AS NOT NULL,
[ContactPerson] [varchar] (50) COLLATE SQL_Latin1_Standard_CP1_CI_AS NOT NULL,
[CompanyAddress] [varchar] (50) COLLATE SQL_Latin1_Basic_CP1_CI_AS NOT NULL,
[City] [varchar] (50) COLLATE SQL_Latin1_Standard_CP1_CI_AS NOT NULL,
[Talk about] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Country] [varchar] (50) COLLATE SQL_Latin1_Basic_CP1_CI_AS NOT NULL,
[Phone] [varchar] (72) COLLATE SQL_Latin1_Standard_CP1_CI_AS NOT NULL,
[email] [varchar] (50) COLLATE SQL_Latin1_Basic_CP1_CI_AS NULL
) ON [Key]
GO
/****** Object: Table [dbo]. [suppliers] ******/
CREATE TABLE [dbo]. [suppliers] (
[SupplierID] [int] Personality (1, 1) NOT NULL,
[CompanyName] [varchar] (120) COLLATE SQL_Latin1_Basic_CP1_CI_AS NOT NULL,
[ContactName] [varchar] (90) COLLATE SQL_Latin1_Standard_CP1_CI_AS NOT NULL,
[ContactTitle] [varchar] (90) COLLATE SQL_Latin1_Basic_CP1_CI_AS NOT NULL,
[Address] [varchar] (180) COLLATE SQL_Latin1_Basic_CP1_CI_AS NOT NULL,
[City] [varchar] (50) COLLATE SQL_Latin1_Basic_CP1_CI_AS NOT NULL,
[Status] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ZipCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Country] [varchar] (50) COLLATE SQL_Latin1_Standard_CP1_CI_AS NOT NULL,
[Phone] [varchar] (72) COLLATE SQL_Latin1_Standard_CP1_CI_AS NOT NULL,
[Fax] [varchar] (72) COLLATE SQL_Latin1_Standard_CP1_CI_AS NOT NULL,
[HomePage] [content material] COLLATE SQL_Latin1_Basic_CP1_CI_AS NULL
) ON [Key] TEXTIMAGE_ON [PRIMARY]
GO
/****** Thing: Table [dbo]. [order_details] ******/
CREATE TABLE [dbo]. [order_details] (
[odID] [bigint] Identification (1, 1) NOT NULL,
[EmployeeID] [int] NOT NULL,
[OrderID] [int] NOT NULL,
[ProductID] [int] NOT NULL,
[UnitPrice] [real] NOT NULL,
[Number] [int] NOT NULL,
[Discount] [real] NULL
) ON [Principal]
GO
/****** Subject: Stand [dbo]. [products] ******/
CREATE TABLE [dbo]. [products] (
[ProductID] [int] Personal information (1, 1) NOT NULL,
[ProductName] [varchar] (120) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[SupplierID] [int] NOT NULL,
[CatID] [int] NOT NULL,
[stockPerUnit] [int] NOT NULL,
[UnitPrice] [real] NOT NULL,
[UnitsInStock] [smallint] NOT NULL,
[ReorderLevel] [smallint] NOT NULL,
[Discontinued] [smallint] NULL
) ON [PRIMARY]
GO
/****** Object: Stand [dbo]. [requests] ******/
CREATE TABLE [dbo]. [purchases] (
[OrderID] [int] Id (1, 1) NOT NULL,
[CustomerID] [int] NOT NULL,
[EmployeeID] [int] NOT NULL,
[ProductID] [int] NOT NULL,
[OrderDate] [datetime] NOT NULL,
[number] [int] NULL,
[RequiredDate] [datetime] NULL,
[ShipperID] [int] NOT NULL,
[ShippedDate] [datetime] NOT NULL,
[Freight] [real] NULL
) ON [Main]
GO
ALTER TABLE [dbo]. [categories] WITH NOCHECK ADD
CONSTRAINT [PK_categories] Key KEY CLUSTERED
(
[CatID]
) ON [Principal]
GO
ALTER TABLE [dbo]. [customers] WITH NOCHECK ADD
CONSTRAINT [PK_custo] Principal KEY CLUSTERED
(
[customerid]
) ON [Key]
GO
ALTER TABLE [dbo]. [employees] WITH NOCHECK ADD
CONSTRAINT [PK_employees] Key KEY CLUSTERED
(
[EmployeeID]
) ON [Key]
GO
ALTER TABLE [dbo]. [shippers] WITH NOCHECK ADD
CONSTRAINT [PK_shippers] Principal KEY CLUSTERED
(
[ShipperID]
) ON [Major]
GO
ALTER TABLE [dbo]. [suppliers] WITH NOCHECK ADD
CONSTRAINT [PK_suppliers] PRIMARY KEY CLUSTERED
(
[SupplierID]
) ON [Key]
GO
ALTER TABLE [dbo]. [order_details] WITH NOCHECK ADD
CONSTRAINT [PK_order_details] Main KEY CLUSTERED
(
[odID]
) ON [Principal]
GO
ALTER TABLE [dbo]. [products] WITH NOCHECK ADD
CONSTRAINT [PK_products] Major KEY CLUSTERED
(
[ProductID]
) ON [Principal]
GO
ALTER TABLE [dbo]. [requests] WITH NOCHECK ADD
CONSTRAINT [PK_purchases] PRIMARY KEY CLUSTERED
(
[OrderID]
) ON [Principal]
GO
ALTER TABLE [dbo]. [order_details] WITH NOCHECK ADD
CONSTRAINT [DF__order_det__Order__7C8480AE] DEFAULT (0) FOR [OrderID],
CONSTRAINT [DF__order_det__Produ__7D78A4E7] DEFAULT (0) FOR [ProductID],
CONSTRAINT [DF__order_det__UnitP__7E6CC920] DEFAULT (0) FOR [UnitPrice],
CONSTRAINT [DF__order_det__Quant__7F60ED59] DEFAULT (1) FOR [Volume],
CONSTRAINT [DF__order_det__Disco__00551192] DEFAULT (0) FOR [Discount]
GO
ALTER TABLE [dbo]. [products] WITH NOCHECK ADD
CONSTRAINT [DF__products__UnitPr__060DEAE8] DEFAULT (0) FOR [UnitPrice],
CONSTRAINT [DF__products__UnitsI__07020F21] DEFAULT (0) FOR [UnitsInStock],
CONSTRAINT [DF__products__Reorde__08EA5793] DEFAULT (0) FOR [ReorderLevel],
CONSTRAINT [DF__products__Discon__09DE7BCC] DEFAULT (0) FOR [Discontinued]
GO
ALTER TABLE [dbo]. [requests] WITH NOCHECK ADD
CONSTRAINT [DF__purchases__Freight__03317E3D] DEFAULT (0) FOR [Freight]
GO
CREATE INDEX [CategoryID] ON [dbo]. [categories]([CatID]) ON [Major]
GO
CREATE INDEX [EmployeeID] ON [dbo]. [employees]([EmployeeID]) ON [Key]
GO
CREATE INDEX [ShipperID] ON [dbo]. [shippers]([ShipperID]) ON [Main]
GO
CREATE INDEX [SupplierID] ON [dbo]. [suppliers]([SupplierID]) ON [Major]
GO
CREATE INDEX [FK_order_details] ON [dbo]. [order_details]([OrderID]) ON [Key]
GO
CREATE INDEX [odID] ON [dbo]. [order_details]([odID], [OrderID], [ProductID]) ON [PRIMARY]
GO
CREATE INDEX [CategoryID] ON [dbo]. [products]([CatID]) ON [Key]
GO
CREATE INDEX [ProductID] ON [dbo]. [products]([ProductID], [SupplierID], [CatID]) ON [Major]
GO
CREATE INDEX [FK_purchases] ON [dbo]. [requests]([EmployeeID]) ON [Main]
GO
CREATE INDEX [OrderID] ON [dbo]. [orders]([OrderID], [CustomerID], [EmployeeID]) ON [PRIMARY]
GO
ALTER TABLE [dbo]. [order_details] ADD
CONSTRAINT [FK_order_details_employees] Overseas KEY
(
[EmployeeID]
) REFERENCES [dbo]. [employees] (
[EmployeeID]
)
GO
ALTER TABLE [dbo]. [products] ADD
CONSTRAINT [FK_products_categories] FOREIGN KEY
(
[CatID]
) REFERENCES [dbo]. [categories] (
[CatID]
),
CONSTRAINT [FK_products_suppliers] FOREIGN KEY
(
[SupplierID]
) REFERENCES [dbo]. [suppliers] (
[SupplierID]
)
GO
ALTER TABLE [dbo]. [orders] ADD
CONSTRAINT [FK_requests_customers] International KEY
(
[CustomerID]
) REFERENCES [dbo]. [customers] (
[customerid]
),
CONSTRAINT [FK_purchases_employees] Overseas KEY
(
[EmployeeID]
) REFERENCES [dbo]. [employees] (
[EmployeeID]
),
CONSTRAINT [FK_orders_products] Overseas KEY
(
[ProductID]
) REFERENCES [dbo]. [products] (
[ProductID]
),
CONSTRAINT [FK_purchases_shippers] Overseas KEY
(
[ShipperID]
) REFERENCES [dbo]. [shippers] (
[ShipperID]
)
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
/****** Object: View dbo. vieworders ******/
CREATE VIEW dbo. VIEW1
AS
SELECT dbo. requests. OrderDate, dbo. requests. ProductID, dbo. orders. EmployeeID, dbo. requests. OrderID, dbo. orders. CustomerID, dbo. products. ProductID AS Expr1,
dbo. products. ProductName, dbo. products. SupplierID, dbo. products. CatID, dbo. shippers. ShipperID, dbo. shippers. CompanyName, dbo. shippers. ContactPerson,
dbo. shippers. CompanyAddress, dbo. shippers. State, dbo. shippers. City, purchases_1. OrderID AS Expr2, requests_1. CustomerID AS Expr3, purchases_1. EmployeeID AS Expr4,
orders_1. ProductID AS Expr5
FROM dbo. purchases INNER JOIN
dbo. products ON dbo. requests. ProductID = dbo. products. ProductID INNER JOIN
dbo. orders purchases_1 ON dbo. products. ProductID = purchases_1. ProductID INNER JOIN
dbo. shippers ON orders_1. ShipperID = dbo. shippers. ShipperID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
/****** Object: Stored Process dbo. insertOrders ******/
create proc insertOrders @customerId varchar(5), @employeeId varchar(5),
@ProductID varchar(5), @requiredDate varchar(5), @shipperId varchar(5),
@freight decimal(10, 2), @quantity int
AS
BEGIN
Declare @reOrderLevel int, @quantityAvailable int;
SET Exchange ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION
select @reorderLevel = reorderLevel, @quantityAvailable=UnitsInStock from products where ProductID = @ProductID;
if(@quantityAvailable<@volume)
begin
print 'Sorry, we have been sold-out for now';
return 0;
end
else
begin
Insert into purchases(customerId, EmployeeId, productId, OrderDate, amount, RequiredDate, ShipperId, ShippedDate, freight)
values(@customerId, @employeeId, @productId, getdate(), @amount, @requiredDate, @shipperId, getdate(), @freight);
update products place UnitsInStock = where ProductID = @ProductID;
if(select UnitsInStock from products where ProductID = @ProductID) <= @reOrderLevel
begin
print 'Re-order limit has been reached, Please re-order stock now!!!!';
end
end
COMMIT TRANSACTION
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
/****** Thing: Trigger dbo. del ******/
CREATE Result in [del] ON dbo. orders
FOR DELETE
AS
Print 'Deletion Of Purchases is not Allowed'
ROLLBACK TRANSACTION
RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Relationship Diagram
