\n Hello,\n <\/p>\n
\n I need some help getting my assignment to the finish line, I have done some work but I need some help to correct some of the sections I have written so far please. Let me know if you are interested and can do the work, see the attachments for the sections that need to be corrected with the feedback I got back from the evaluator.\n <\/p>\n
\n Thanks\n <\/p>\n<\/div>\n
Hello, I need some help getting my assignment to the finish line, I have done some work but I need some help to correct some of the sections I have written so far please. Let me know if you are inter
\n Western Governor\u2019s University Data Management \u2013 Applications \u2013 C170 SIMBIAT AJIKANLE VHT2 \u2013 VHT2 TASK 1: NORMALIZATION AND DATABASE DESIGN 06\/05\/2022 A. A standardized record model that shows the ordering procedure forNora\u2019s Bagel Bina. Assignment of each feature from the 1NF table into the 2NF tableb. The correlation amongst the two 2NF. Data cardinality in the dotted cells in respect to one-to-one, one-to-many, and many-to-one or many-to-many represented as M: M, 1:1, 1:M, and M:1, respectively. Answer: Part a and b Second Standard Form (2NF) BAGEL ORDERBAGEL ORDER LINE ITEMBAGELPKBagel Order IDFK\/PK Bagel Order IDPKBagel IDDate of the Order 1:MFK\/PKBagel IDM:1Bagel NaFirst NameBagel QuantityBagel DescriptionLast NameBagel Price1st Address 2nd Address State CityMobile Phone ZipDelivery FeeSpecial Notesc. Explanation of how the allocated features to the 2NF tables and the determination of the cardinality of the correlations between the 2NF tables Answer: The cardinality of Bagel Order to Bagel Order Line item is one-to-many because there can be many line items to the one bagel order. The cardinality of Bagel Order Line Item to Bagel is many-to-one because multiple orders can include a single bagel, or a single bagel type can be in many BOLI’s. However, a single bagel type will only be one line item. 2.Standard form (3NF) of Nora Bagel Bin Database Blueprintsa. Assignment of each features from the 2NF Bagel Order table the new 3NF tables.b. Provision of each of the 3NF table with a title that reflect its contentsc. Creation of a field that links the two 3NF tables named in part A2b. d. Description of the relationships between the 3NF tables by indication of their cardinality in the dotted cells including one-to-one, one-to-many, many-to-one, or many- to-many represented as 1:1, 1:M, M:1, and M:M, respectively. Answer: Answer of part a to d:Third Standard Form (3NF)BAGEL ORDER LINE ITEMBAGEL ORDER LINE ITEMBAGELPKBagel Order IDPK \/ FKBagel Order IDPKBagel IDFKCustomer ID1:MPK \/ FKBagel IDM:1Bagel NameOrder DateBagel QuantityBagel DescriptionSpecial NotesBagel PriceDelivery FeeM:1CUSTOMERPKCustomer IDFirst NameLast NameFirst Address Second Address CityState Mobile PhoneZipe. Explanation of how the attributes were assigned to the 3NF tables and determination of the cardinality of the correlations between 3NF tables Answer: The cardinality of Bagel Order Info to Bagel Order Line Item is one to many because one bagel order can contain many line items. The cardinality of Bagel Order Line Item to Bagel is many-to-one because multiple orders can include a single bagel, or a single bagel type can be in many BOLI’s. However, a single bagel type will only be one line item. The cardinality of Bagel Order Info to Customer is many to one. It is this way because there will always be at least one order to customer, while a single customer may make many orders. 3.Completion of the “Final Database Model” of the \u201cNora\u2019s Bagel Bin Database Blueprints\u201d a. Renaming of the featuresb. Assignment of one of the 5 data kinds to each feature in the 3NF tables: TIMESTAMP, CHAR ( ), VARCHAR(), NUMERIC or INTEGER (). Here, Each data kind has been used at least once Answer: Answer of part a to d:Final Physical Database ModelBAGEL ORDER INFOBAGEL ORDER LINE ITEMBAGELPKBagel Order IDINTPK \/FKbagel_order_idINTPKBagel IDCHAR(2)FKcustomer_idINT1:MPK \/ FK bagel_idCHAR(2)M:1Bagel Nam e CHAR(20)Order dateTIMESTAMPBagel_quantityINTBagel Descriptio n VARCHAR(1 8 0)special_notesVARCHAR(18)Bagel PriceNUMERIC(3, 2 )Delivery_feeNUMERIC(4,2)M:1CUSTOMERPKcustomer_idINTfirst_nameVARCHAR (20)last_nameVARCHAR (20)1st address VARCHAR (20)2nd address VARCHAR (20)zipVARCHAR (20)cityVARCHAR (20)stateINTmobile_phoneINT B. Creation of a record using the “Jaunty Coffee Co. ERD” Jaunty Coffee Co. ERD 1. Development of SQL code to create each table as indicated in the attached \u201cJaunty Coffee Co. ERD\u201d a. SQL code to create all the tables. Create Table\u2019s: 1) Employee Employee_id INTEGER, 1 st _name VARCHAR (30), last_name VARCHAR (30), hire_date, shop_id INTEGER, job_title VARCHAR(30), PRIMARY KEY (employee_id),FOREIGN KEY (shop_id) REFERENCES Coffee_Shop(shop_id));2)Coffee_Shop Shop_id INTEGER, city VARCHAR(50),shop_name VARCHAR(50), state CHAR(2), PRIMARY KEY (shop_id) );3)Coffee Coffee (coffee_id INTEGER, supplier_id INTEGER, shop_id INTEGER, coffee_name VARCHAR(30), PRIMARY KEY (coffee_id), price_per_pound NUMERIC(5,2), Coffee_Shop (shop_id),FOREIGN KEY (shop_id) REFERENCESFOREIGN KEY (supplier_id) REFERENCES Supplier (supplier_id) );4) Supplier Supplier_id INTEGER, country VARCHAR(30),company_name VARCHAR(50), sales_contact_name VARCHAR(60), PRIMARY KEY (supplier_id) email VARCHAR(50) NOT NULL);b. Demonstration of the tested code using a screenshot showing SQL commands and the database server\u2019s response. Coffee:Coffee_Shop: Employee: Supplier:2. Development of SQL code to populate each table in the database design document a. Provision of the SQL code to populate the tables with three rows of data in each table. Insert DataINSERT INTO EmployeeVALUES (0104, ‘Grant’, ‘Samson’, ‘1997-05-12’, ‘Manager’, 1234),(8652, ‘Susan’, ‘Ryan’, ‘2015-12-07’, ‘Assistant Manager’, 4506),(8741, ‘John’, ‘Adams’, ‘2021-03-29’, ‘Barista’, 9832);INSERT INTO Coffee_ShopVALUES (4506, ‘Lil Beans’, ‘Sacramento’, ‘CA’), (9832, ‘Bitter N Sweet’, ‘Buffalo’, ‘NY’), (1234, ‘Beans4You’, ‘Boise’, ‘ID’);INSERT INTO Coffee VALUES (9517, 9832, 2345, ‘Brazillian Dark’, 7.50),(7745, 9832, 7485, ‘Columbian Select Dark’, 6.32),(2001, 1234, 6521, ‘Columbian Light Roast’, 4.75);INSERT INTO SupplierVALUES (2345, ‘Beans, Beans, Beans’, ‘Brazil’, ‘Bruno Silva’, ‘[email\u00a0protected]’), (7485, ‘We Mean Beans’, ‘Columbia’, ‘Pedro Sanchez’,'[email\u00a0protected]’),(6521, ‘Great Beans’, ‘USA’, ‘Jessica Johnson’, ‘[email\u00a0protected]’) ;b. Demonstrate that you tested your code by providing a screenshot showing your SQL commands and the database server\u2019s response. Answer: Attached screenshot.3. Development of SQL code to create a view: a. SQL code to create a view. The view should exhibit all of the information from the \u201cEmployee\u201d table but should concatenate each employee\u2019s first and last name. it should be formatted with a space between the first and last name and presented as a new attribute called employee_full_name. CREATE VIEW EmpView AS SELECT employee_id, CONCAT (first_name, ‘ ‘, last_name) employee_full_name, job_title ,hire_date, ,shop_id FROM Employee; c. Demonstration of the tested code by providing a screenshot showing SQLCommands and the database server\u2019s response. Answer: Attached screenshot . 4. SQL code to create an index on the coffee_name field.a. The SQL code to create an index on the coffee_name field fromthe \u201cCoffee\u201d table.CREATE INDEX idx_coffee_name ON Coffee (coffee_name) b. Testing of the code by providing a screenshot showing SQL commands and the database server\u2019s response. Answer: Attached screenshot .5. SQL code development to create an SFW (SELECT\u2013FROM\u2013WHERE) query for any of the tables or views a. SQL code to create SFW querySELECT employee_id, first_name, last_name FROM Employee WHERE shop_id = 1234;b. Demonstration that the code was tested by providing a screenshot showing SQL commands and the database server\u2019s response. Answer: Attached screenshot 6. SQL code to create a query a. SQL code to create table joins query. The query joins together three different tables and include attributes from all three tables in its output. SELECT coffee_id, coffee_name, A.shop_id, shop_name, city, state, C.company_name AS ‘Supplier’, sales_contact_name, c.email FROM Coffee AS A LEFT JOIN Coffee_Shop AS B ON A.shop_id = B.shop_id INNER JOIN Supplier AS C ON A.supplier_id = C.supplier_id WHERE price_per_pound < 6b. Demonstration the code was tested by providing a screenshot showing SQL commands and the database server\u2019s response. Answer: Attached screenshot .\n<\/p>\n","protected":false},"excerpt":{"rendered":"
Hello, I need some help getting my assignment to the finish line, I have done some work but I need some help to correct some of the sections I have written so far please. Let me know if you are interested and can do the work, see the attachments for the sections that need to […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":"","_joinchat":[]},"categories":[1],"tags":[],"_links":{"self":[{"href":"https:\/\/qualityassignments.net\/wp-json\/wp\/v2\/posts\/243902"}],"collection":[{"href":"https:\/\/qualityassignments.net\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/qualityassignments.net\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/qualityassignments.net\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/qualityassignments.net\/wp-json\/wp\/v2\/comments?post=243902"}],"version-history":[{"count":1,"href":"https:\/\/qualityassignments.net\/wp-json\/wp\/v2\/posts\/243902\/revisions"}],"predecessor-version":[{"id":244354,"href":"https:\/\/qualityassignments.net\/wp-json\/wp\/v2\/posts\/243902\/revisions\/244354"}],"wp:attachment":[{"href":"https:\/\/qualityassignments.net\/wp-json\/wp\/v2\/media?parent=243902"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/qualityassignments.net\/wp-json\/wp\/v2\/categories?post=243902"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/qualityassignments.net\/wp-json\/wp\/v2\/tags?post=243902"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}