SMART FOOD VENTURE (SFV) is a wholesale company supplying meatchicken that mainl

Question

SMART FOOD VENTURE (SFV) is a wholesale company supplying meatchicken
that mainly concentrates around Manjung and few other townships in the
state of Perak. Besides supplying to government departments, SFV also
compete in the open market business all over the state of Perak. As a
commodity, meat-chicken pricing varies and fluctuate on daily basis ranging
between 3% to 10% and this reflect a need to have a efficient and accurate price
fixing. Due to competition from other suppliers, bargaining and credit control
require a lot of time for negotiation to be done through phone calls and this
causes high telephone bill. A new E-pasar initiative is suggested to have a SMSbase
business negotiation system which would be able to overcome all our daily
business routine needs in a “Sell-Buy” environment. By improving the customer
service satisfaction, we hope to increase the marketing ability especially with the
existing bulk volume customers and also to attract other new business potentials.
From your findings, you concluded the specific requirements of SFV’s strategic
objective to monitor service levels to achieve excellence were:
• To reduce telephone cost and time for price negotiation.
• Providing 24-hour On-line services for our customers (Price
negotiation, Order, Account update).
• Able to provide an excellent service by having a direct price
negotiation with the on duty manager at anytime.
• Prepare an acurate and up-to-date order list before the operational
begins.
System development criteria:
• Customer registration can only be done from computer terminal to the
cuatomer database.
• Daily entry of the input of the commodity price to the computer
database by authorized person.
• Customers are provided with a two-way communication with the e-
Pasar system; Negotiated price and e-order.
• Daily price negotiation is done between the customer and the
marketing manager by phone.
• Marketing manager would be able to change the discount table in the
database through SMS so the customer would receive the new
negotiated price before an order is made.
• Discount databse entry would be only done through the computer
terminal.
There are the following four (4) region of functionality module in the e-Pasar
software system proposal:
1. SMS Order Management Module
2. Operation Administration Module
3. Stock Update Alert Module
4. Balance Notification Interface Module
At all stages, the aims were to keep processes and documents simple and clear
and to integrate them with existing workflows, documents and contacts whenever
possible.
Make your own relevant or needed assumptions in answering the following
question based on the above case study scenario.
You are encouraged to use the SQL server 2008 to prepare you answers. You
can submit your codes and the database that you created which would be used
for higher evaluation.

Question 1
Based on the above case study, prepare a complete set of tables that would be needed to
support SFV to develop system that would enable the storage of all the information.
Identify all the important attributes for all the tables that you suggest. Provide a suitable
data type for each of attributes to reflect the usability.
Develop a normalized database for all the tables and you should show an example of each of
the 1st, 2nd and 3rd normalization process involved for your tables.

Question 2
(a) For each of the tables created in question 1, identify all the primary keys, foreign
keys and also any composite keys that would be used to maintenance the
database.

(b) Write the SQL commands to be used to create each of the tables that you would
included in the creation of the database for SFV. Assume you own constraints
and data verifications.

Question 3
Analyse the case study and identify the entire possible user requirement for SVF’s daily
and monthly operations as described above. List them down as different relationship
with possible type of cardinality.
Draw an estimated complete ER diagram that would be able to represent SVF’s complete
database.

Question 4
(a) Insert some of the appropriate data for each of the tables that you had created in
the database. Use the SQL commands to insert 5 different tuples for each of the
tables.

(b) Create your own queries (10) that would be used by the SVF’s management team
to help them in their daily activities of using the database. Use the SQL
commands to write your queries and provide the answer from you sample data
above.

Explanation / Answer

Hi, Here are the basic tables that you can create .

create table products
( id int not null auto_increment primary key
, product_number varchar(24) NULL
, product_name varchar (99) not null
);

create table product_prices
( id int not null
, product_id int not null
, price decimal(6,2)
, currency_code char(3)
, creation_date datetime null
,CONSTRAINT `Fk_prod_price` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
)

create table product_attributes
( id int not null
, product_id int not null
, attribute_name varchar(99)
, attribute_value varchar(24) not null
, attribute_unit_of_measurement varchar(6) NULL
,CONSTRAINT `Fk_prod_Attr` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
)

create table customer_details
( id int not null
, product_id int not null
, customer_lastname varchar(99)
, customer_firstname varchar(99)
, customer_middlename varchar(99)
, township varchar(50) not null
, phonenumber varchar(20) NULL
, address varchar(20) NULL
, address2 varchar(20) NULL
, zipCode varchar(20) NULL
, city varchar(20) NULL
,CONSTRAINT `Fk_prods` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
)

create table customer_communication
( id int not null
, cust_id int not null
, SMS_text varchar(2000)
, creation_date datetime null
, is_sent tinyint(2) not null
, is_notified tinyint(2) not null
,CONSTRAINT `Fk_CustDetails_id` FOREIGN KEY (`cust_id`) REFERENCES `customer_details` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
)

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

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