7. Convert the database into its 2nd Normal Form (2NF). Plases label your tabies as they appose in the ERD slobtch. 7.1 Entity Ralationchip Diagram 7.2 2NF Raquiramantc To transition from 1NF to 2NF, we need all columns in all tabies to not have a partiel depondancy on the PK of the table. This means that any tabies which only Barcode, Navigationpath, or Iterdescription - In moving to 2NF we wil seperate out each of these as a primary key (for fweir own respection tabies), and any columns dopendant on one of these columns but not the others, will go into that table. The only columns mamaining in the tabies wil be fully functionally dependint on the primary key of the table. For exampla, Thernby, we remova the partial dependency, and wil be in 2NF. Was ans instucted to craabe a unigue idertifier for the Transactions, 2NF. 8eoond normal form notec - Nobce that Total is only depondant on CartiD and not on \&CartiD, barcodes. This is a partial dependency - Gocond nomal form required no earral functionsl dependency an the kay. - This can be achiovad in 2 ways: create a unique identifer per row (so that there is a simpla primary loyk, or break the tabia up into only the polumns ahich are fully dependiant on the compoate loey. - We are going to opt for the easiar solution, and craabe a unique simple primary kay on the Transactians_1hf. Wa can craste a unique primary kay by concatenating CartID||barcode - Na - this approach doos maka t mare chalkenging to gat into 3NFI Which would be a good argumart to opt far tha latter approach. 7.3 Data Anomalles You may stumble into an error whon you insert the data which violates this constraint. You query this with the data owner, and you are told that then are thren trananctions in the databese which ware crrors. You are infomed that al transactlons in the databsese comerpond to prooucts are in the database. Those ontrics wore osptured incomectly, and must be nemoved. How will you desl with this? \begin{tabular}{|l|l|} 1 & Wiread_sq1 \\ 2 & select count(distinct barcode) fren Transactions_iNF where barcode not in (select barcode fron products_1NF) \end{tabular} Query started at 12:38:44 AN UTC-11; Query enecuted in a.86 n 7. Convert the database into its 2nd Normal Form (2NF). Plases label your tabies as they appose in the ERD slobtch. 7.1 Entity Ralationchip Diagram 7.2 2NF Raquiramantc To transition from 1NF to 2NF, we need all columns in all tabies to not have a partiel depondancy on the PK of the table. This means that any tabies which only Barcode, Navigationpath, or Iterdescription - In moving to 2NF we wil seperate out each of these as a primary key (for fweir own respection tabies), and any columns dopendant on one of these columns but not the others, will go into that table. The only columns mamaining in the tabies wil be fully functionally dependint on the primary key of the table. For exampla, Thernby, we remova the partial dependency, and wil be in 2NF. Was ans instucted to craabe a unigue idertifier for the Transactions, 2NF. 8eoond normal form notec - Nobce that Total is only depondant on CartiD and not on \&CartiD, barcodes. This is a partial dependency - Gocond nomal form required no earral functionsl dependency an the kay. - This can be achiovad in 2 ways: create a unique identifer per row (so that there is a simpla primary loyk, or break the tabia up into only the polumns ahich are fully dependiant on the compoate loey. - We are going to opt for the easiar solution, and craabe a unique simple primary kay on the Transactians_1hf. Wa can craste a unique primary kay by concatenating CartID||barcode - Na - this approach doos maka t mare chalkenging to gat into 3NFI Which would be a good argumart to opt far tha latter approach. 7.3 Data Anomalles You may stumble into an error whon you insert the data which violates this constraint. You query this with the data owner, and you are told that then are thren trananctions in the databese which ware crrors. You are infomed that al transactlons in the databsese comerpond to prooucts are in the database. Those ontrics wore osptured incomectly, and must be nemoved. How will you desl with this? \begin{tabular}{|l|l|} 1 & Wiread_sq1 \\ 2 & select count(distinct barcode) fren Transactions_iNF where barcode not in (select barcode fron products_1NF) \end{tabular} Query started at 12:38:44 AN UTC-11; Query enecuted in a.86 n
