Question

Question 1

Figure 1 below shows part of the review of the night cream by a beauty blogger in her beauty review database (Microsoft Access).

Figure 1: “Cosmetics” table

(a) Which field is the most appropriate field to act as the primary key of the given table? Give explanation to your answer.

(b) What should be the data type for the field “Recommendation”?

(c) If “Number” were set as the data type of “Price” and “Weight”, which data type should be used for the field size respectively?

(d) If the recommendation must be provided to complete the review, suggest the field expression of validation rule for that.

(e) Other than using validation rule, suggest another field property to achieve the same result in question part (d).

Copy the following Query-by-example (QBE) template as shown in Figure 2 when answering parts (f) and (h) of this question. Note that you are required to create ONE QBE table for EACH question and fill in the necessary entries.

Figure 2: QBE template

(f) Design a query to display ALL fields for the products with 2 stars.

(g) Design a query to display ONLY “Brand”, “Price” and “Recommendation” by allowing the users to enter the skin type with a prompting message “Please specify your skin type”.

(h) Design a query to display ONLY “Brand”, “Skin” and “Feature” for products with “Brand” staring with “S” OR weight larger than 100g.

Question 1 Figure I below shows part of the review of the night cream by a beauty blogger in her beauty review database (Microsoft Access). Cosmetics ProductID - Recommendation 001 Brand Buty Girly 150 2 00 Out Sali Price HKDS) Skin . Feature - Weih) Dry Moisturizing 999 Normal Shiny 2012.9 Problematik 150 599 A 100 1909 Dry RECOVITY 150 399 AU Figure 1: "Cosmetics" table (a) Which field is the most appropriate field to act as the primary key of the given table? Give explanation to your answer. (b) What should be the data type for the field "Recommendation"? (c) If "Number" were set as the data type of "Price" and "Weight", which data type should be used for the field size respectively? (d) If the recommendation must be provided to complete the review, suggest the field expression of validation rule for that. (e) Other than using validation rule, suggest another field property to achieve the same result in question part (d). Copy the following Query-by-example (QBE) template as shown in Figure 2 when answering parts (1) and (h) of this question. Note that you are required to create ONE QBE table for EACH question and fill in the necessary entries. Show Criteria Figure 2: QBE template (1) Design a query to display ALL fields for the products with 2 stars. (g) Design a query to display ONLY "Brand","Price" and "Recommendation" by allowing the users to enter the skin type with a prompting message "Please specify your skin type". (h) Design a query to display ONLY "Brand". "Skin" and "Feature" for products with "Brand" staring with "S" OR weight larger than 100g.

Answer 1

Question 1: For the given table ProductId" can be used as a primary key. • Primary key must be unique • ProductId is unique for each and every product and will not be null. b) • For the given table datatype of the field recommendation attribute "varchar" is the appropriated data type to use it contains '*' which is a character. • It accepts only the string values. Examples: create table cosmetics(Product_Id int(3) Recommendation varchar(5)); insert into cosmetics values(001,'**); insert into cosmetics values(002, *****"); insert into cosmetics values(003,'**'); insert into cosmetics values(004,'*****'); insert into cosmetics values(005,'***); insert into cosmetics values(006,'*'); sql query: select * from cosmetics;

Output Product_id Recommendation ***** dataType for field price and weight: • The datatype long(precision.scale) can be set ot field size • For price dataType will be Number(4.1) • For weight dataType will be int(3) For precision determies the number of digits a • Scale denotes the number of digits to the rght side of the decimal Examples: create table cosmetics(ProductId int(3).price numeric(4.1), Weight int(3)); insert into cosmetics values(001,99.9,150); insert into cosmetics values(002,99.9,120); insert into cosmetics values(003,239,150); insert into cosmetics values(004,59.9,100); insert into cosmetics values(005.159.9,150): insert into cosmetics values(006,39.9,75);

sql query select * from cosmetics; Productid price 99.9 99.9 239 59.9 159.9 39.9 Field expression of validation rule: /[0-9]+/+/ [a-zA-Z]+$/ +/[0-9]+/+// [a-zA-Z]+$/ + /^[a-zA-Z]+$/ + /[0-9]+/ • Recommendation attribute use only '*' as the input • For validation of column recommendation : recommendation_col not null; • Values should be {'*','**','***','****','*****'} Recommendation statetment: CONSTRAINT recommendation_chck CHECK(Recommendation IN (***********************')) (OR) After creating the table recommendation statement: ALTER TABLE Cosmetics ADD CONSTRAINT Recommendation CHECK (last_name IN ('*"****** *********));