Database Management Systems

I need help with my Database Management Systems.

This task represents the database design for a part of the food delivery function at Jack and

Annie’s Soup & Sandwich Service. The outcome of this process represents a portion of the database

design for the system that you are working on. It will also serve as the basis for the SQL and Python

tasks. The report should include the following:

 Logical database design

This forms the initial database schema for the food delivery function at Jack and Annie’s Soup &

Sandwich Service. It is based on the accompanying data model. The data model does not cover all of

the delivery processes. It is assumed that the mapping is to a relational schema. For each table, you

need to specify the columns that comprise the table, identify its primary key, and specify any foreign

keys, including columns that serve as both primary and foreign keys. The tables should be presented

in standard form. You should ensure that common column have identical names. See the handout

for what constitutes a logical database design – it is not a diagram.

 Changes to logical database design

This represents changes to the logical database design based on the restructuring of columns,

consolidation of columns, and any denormalization of the tables. This may be presented as a bullet

list – see the handout for reference.

 Final database design

This represents changes to the final database design you are proposing after the application of the

changes listed earlier. This should be presented in standard form. Remember to depict all keys,

including columns that are both primary and foreign keys. Once again, this is not a diagram.

 Physical database design

This includes the physical schema and the list of indexes to be used. You need to define each

column, specifying its format, (type and length), and whether it excludes NULL values. Also, specify

any indexes, and all forms of referential integrity that may apply. This is to be done using the

standard SQL notation (for both tables and indexes), as illustrated below:

CREATE TABLE CREDITCARD

( CCNO CHAR(16) NOT NULL,

TYPE CHAR(4),

EXPIRY DATE DATE,

CARDHOLDER NAME VARCHAR(30),

CUSTOM CHAR(8),

PRIMARY KEY ( CCNO ),

FOREIGN KEY ( CUSTID ) REFERENCES CUSTOMER )

where

 foreign keys must reference another table

 multiple foreign keys must be defined separately

 concatenated primary keys must list all columns separated by commas.

INDEX PROD_INDEX ON PRODUCT ( DESCR ASC )

Assumptions

This section covers the assumptions that you have made in assembling the report. Include this before

the logical database design, so that I can better understand the thinking that went into the creation of

the report. Assumptions could cover:

 Identification of primary keys. This is particularly important for associative entities, especially if

the concatenation of keys from the participating entities is not sufficient to serve as a unique

identifier.

 Identification of foreign keys. Particularly for 1:1 relationships.

 Mapping of subclass structure. Include justification for the option selected.

 Tables/columns discarded in the final design. This is an obvious one.

 Table layout. As a general rule, keys should appear at the front. Composite attributes must be

sequenced together. Other columns may be arranged as you see fit.

 Column formats. This should address the data type and length, if nonstandard.

 Indexes selected/omitted. Provide reasoning/explanation as to why you felt that is important to

create an index on this column (or set of columns).

Jack and Annie’s Soup & Sandwich Service Delivery Data Model

Database Programming SQL Database Administration MySQL Database Development

Project ID: #33326648

About the project