Question

You are required to create a sales and marketing management system with a small number of common sense attributes. The system should contain the following information  Sales details  Marketing details  Customer details  Production details  Delivery details  Management details

So consider what sales and marketing system is required to deliver a consistent and customer oriented service to all customers over the world. You need to identify the tables you require and the information that they are to contain. This is only a small system, do not over-complicate it.

Create an entity-relationship (ER) model drawn with MS WORD or POWER POINT if you prefer. You are required to submit a print out of your database schema:  Set of normalised tables – with your sample data.  Indicate the table relationships using multiple keys; PK, FK, and SK using UML notation.

2 2. Use MYSQL to create the set of database tables of the relational database model and complete the associated queries given. Procedure:

1) Write all the SQL statements, necessary to create all tables and relationships, with Primary & Foreign keys.

2) Execute each statement in the correct order to create the relational database in MYSQL.

3) Insert some data into each table.

4) Use all your SQL create and Insert statements (from MS Word) to execute in the MYSQL WorkBench

5) Write in MS Word and execute in MYSQL WorkBench the statements necessary to; i. display all tables, ii. identify sales total for each item iii. identify delivery confirmation of sold items iv. identify marketing level for sold items

3. Using MS-ACCESS, link to the MYSQL database and create forms to enter sales and purchase orders. 

No buttons are necessary, just what ACCESS generates to scroll forward, add a record etc.

 Make it clear what the forms do in the Form by adding text or headers.

Answer 1

This example will give you the understanding of the concept ,along with E-R diagram .

Candidate key that key which can uniquely identify all the other attributes of that table.

Superkey is the superset of Candidate Key.

One of the derived C.K.will be considered as primary key,rest will be alternate keys.