Question

Consider the following scenario:

An electronics manufacturer produces a range of consumer and commercial grade electronic devices. These include television sets, sound systems, computer monitors, mobile phones and many other products. Each of these products has a name and a unique ID number associated with them.

Components to make these devices can be purchased from a single vendor or from multiple vendors. Each component has a name, unique ID number and may include a component description (this information is optional).

The manufacturer sells its products to commercial buyers and also directly to consumers. The manufacturer keeps track of the name, address and phone numbers of the commercial buyers. Only the postcode for non-commercial buyers is stored.

a) Your first task is to construct an Entity-Relationship diagram that captures entities and their relationships. You are free to make assumptions about any information that you believe is not supplied as long as you state these assumptions. (Your assumptions should not contradict the description!)

b) Translate the E-R diagram from part (a) into a database schema in SQL. * Your DDL statements should define foreign keys that reflect the relationships outlined in the scenario. * Your foreign key contains should have appropriate referential triggered actions defined. Include an explanation of the referential actions. * Make sure that you select reasonable types for each table's attributes

c) The manufacturer requires that all phone numbers from commercial buyers include the country code (e.g. for Australia this will be +61). How can you use the your database to enforce this rule? Include an appropriate SQL statement(s) in your answer.

Answer 1

a)

Process Documentation:

Process documentation is an easy-to-use tool for improving efficiency. Whether your goal is to reduce training time, improve quality control or meet regulatory requirements, your organization will benefit by using SmartDraw to document its processes.

Lean:

Lean means placing your organization's focus on two things: maximizing customer value while minimizing waste. Implementing a Lean strategy requires clear, concise thinking. This is best accomplished using fast.

Project Management:

SmartDraw simplifies the process of managing projects. The automated design makes SmartDraw as easy to use as a word processor but produces perfect, presentation-quality visual diagrams. Switch between project chart.

Strategic Planning:

Successful companies know that strategic planning is critical to creating competitive advantage and long-term success. SmartDraw gives you all the tools you need to collect and process data, then plan and implement your strategy successfully. It comes out presentation-ready and is easy to publish.

Meetings:

SmartDraw is the perfect electronic whiteboard to use with an online meeting platform such as WebEx™ or GoToMeeting™. A mind map allows anyone to quickly and easily capture plans and decisions made during a meeting. Charts and diagrams can easily be built, modified and shared in real time.

Presentations:
SmartDraw makes it easier than ever to create professional-looking, visual presentations. With just a click, you can send a complete presentation.

b)

ER diagram gives us the good knowledge about the requirement and the mapping of the entities in it, we can easily convert them as tables and columns. i.e.; using ER diagrams one can easily created relational data model, which nothing but the logical view of the database.

There are various steps involved in converting it into tables and columns. Each type of entity, attribute and relationship in the diagram takes their own depiction here. Consider the ER diagram below and will see how it is converted into tables, columns and mappings.basic rule for converting the ER diagrams into tables is

Convert all the Entities in the diagram to tables.

All the entities represented in the rectangular box in the ER diagram become independent tables in the database. In the below diagram, STUDENT, COURSE, LECTURER and SUBJECTS forms individual tables.

All single valued attributes of an entity is converted to a column of the table All the attributes, whose value at any instance of time is unique, are considered as columns of that table. In the STUDENT Entity, STUDENT_ID, STUDENT_NAME form the columns of STUDENT table. Similarly, LECTURER_ID, LECTURER_NAME form the columns of LECTURER table.

Key attribute in the ER diagram becomes the Primary key of the table.STUDENT_ID, LECTURER_ID, COURSE_ID and SUB_ID are the key attributes of the entities. Hence we consider them as the primary keys of respective table.

Declare the foreign key column, if applicable.In the diagram, attribute COURSE_ID in the STUDENT entity is from COURSE entity. Hence add COURSE_ID in the STUDENT table and assign it foreign key constraint. COURSE_ID and SUBJECT_ID in LECTURER table forms the foreign key column. Hence by declaring the foreign key constraints, mapping between the tables are established.

Any multi-valued attributes are converted into new table.A hobby in the Student table is a multivalued attribute. Any student can have any number of hobbies. So we cannot represent multiple values in a single column of STUDENT table. We need to store it separately, so that we can store any number of hobbies, adding/ removing / deleting hobbies should not create any redundancy or anomalies in the system. Hence we create a separate table STUD_HOBBY with STUDENT_ID and HOBBY as its columns. We create a composite key using both the columns.

Any composite attributes are merged into same table as different columns.In the diagram above, Student Address is a composite attribute. It has Door#, Street, City, State and Pin. These attributes are merged into STUDENT table as individual columns.

One can ignore derived attribute, since it can be calculated at any time.In the STUDENT table, Age can be derived at any point of time by calculating the difference between DateOfBirth and current date. Hence we need not create a column for this attribute. It reduces the duplicity in the database.

Converting Weak Entity

Weak entity is also represented as table. All the attributes of the weak entity forms the column of the table. But the key attribute represented in the diagram cannot form the primary key of this table. We have to add a foreign key column, which would be the primary key column of its strong entity. This foreign key column along with its key attribute column forms the primary key of the table.

In our example above, SUBJECTS is the weak entity. Hence, we create a table for it. Its attributes SUBJECT_ID and SUBJECT_NAME forms the column of this table. Although SUBJECT_ID is represented as key attribute in the diagram, it cannot be considered as primary key. In order to add primary key to the column, we have to find the foreign key first. COURSE is the strong entity related to SUBJECT. Hence the primary key COURSE_ID of COURSE is added to SUBJECT table as foreign key. Now we can create a composite primary key out of COURSE_ID and SUBJECT_ID.

Representing 1:1 relationship

Imagine SUBJECT is not a weak entity, and we have LECTURER teaches SUBJECT relation. It is a 1:1 relation. i.e.; one lecturer teaches only one subject. We can represent this case in two ways

Create table for both LECTURER and SUBJECT. Add the primary key of LECTURER in SUBJECT table as foreign key. It implies the lecturer name for that particular subject.
Create table for both LECTURER and SUBJECT. Add the primary key of SUBJECT in LECTURER table as foreign key. It implies the subject taught by the lecturer.
In both the case, meaning is same. Foreign key column can be added in either of the table, depending on the developer’s choice.

Representing 1:N relationship

Consider SUBJECT and LECTURER relation, where each Lecturer teaches multiple subjects. This is a 1: N relation. In this case, primary key of LECTURER table is added to the SUBJECT table. i.e.; the primary key at 1 cardinality entity is added as foreign key to N cardinality entity

Representing M:N relationship

Consider the example, multiple students enrolled for multiple courses, which is M:N relation. In this case, we create STUDENT and COURSE tables for the entities. Create one more table for the relation ‘Enrolment’ and name it as STUD_COURSE. Add the primary keys of COURSE and STUDENT into it, which forms the composite primary key of the new table.

That is, in this case both the participating entities are converted into tables, and a new table is created for the relation between them. Primary keys of entity tables are added into new table to form the composite primary key. We can add any additional columns, if present as attribute of the relation in ER diagram.

Consider the example of HOD and Lecturers. Here one of the Lecturers is a HOD of the department. i.e.; one HOD has multiple lecturers working with him. In this case, we create LECTURER table for the Lecturer entity. Create the columns and primary keys as usual. In order to represent HOD, we add one more column to LECTURER table which is same column as primary key, but acts as a foreign key. i.e.; LECTURER_ID is the primary key of LECTURER table. We add one more column HOD, which will have LECTURER_ID of the HOD. Hence LECTURER table will show HOD’s Lecturer ID for each Lecturer. In this case, primary key column acts as a foreign key in the same table.

Self Referencing M:N relation

Consider Student and Teacher example as ‘Manny students have Many Teachers teaching the subjects’. Here relation between Student and Teacher is M:N. In this case, create independent tables for student and teacher, and set their primary keys. Then we create a new table for the relationship ‘have’ as STUDENT_TEACHER, which will have student and teacher combination, and any other columns if applicable. Basically, student-teacher combination is the two primary key columns from respective tables, hence establishing the relationship between them. Both the primary keys from both tables act as a composite primary key in the new table. This reduces the storing of redundant data and consistency in the database.

c)

No Constraints on the Foreign Key

When no other constraints are defined on the foreign key, any number of rows in the child table can reference the same parent key value. This model allows nulls in the foreign key.This model establishes a "one-to-many" relationship between the parent and foreign keys that allows undetermined values (nulls) in the foreign key. An example of such a relationship is shown in Figure 4-3 on page 8 between the employee and department tables. Each department (parent key) has many employees (foreign key), and some employees might not be in a department (nulls in the foreign key).

NOT NULL Constraint on the Foreign Key

When nulls are not allowed in a foreign key, each row in the child table must explicitly reference a value in the parent key because nulls are not allowed in the foreign key. However, any number of rows in the child table can reference the same parent key value.

This model establishes a "one-to-many" relationship between the parent and foreign keys. However, each row in the child table must have a reference to a parent key value; the absence of a value (a null) in the foreign key is not allowed. The same example in the previous section can be used to illustrate such a relationship. However, in this case, employees must have a reference to a specific department.

UNIQUE Constraint on the Foreign Key

When a UNIQUE constraint is defined on the foreign key, one row in the child table can reference a parent key value. This model allows nulls in the foreign key.

This model establishes a "one-to-one" relationship between the parent and foreign keys that allows undetermined values (nulls) in the foreign key. For example, assume that the employee table had a column named MEMBERNO, referring to an employee's membership number in the company's insurance plan. Also, a table named INSURANCE has a primary key named MEMBERNO, and other columns of the table keep respective information relating to an employee's insurance policy. The MEMBERNO in the employee table should be both a foreign key and a unique key:

To enforce referential integrity rules between the EMP_TAB and INSURANCE tables (the FOREIGN KEY constraint)
To guarantee that each employee has a unique membership number (the UNIQUE key constraint)
UNIQUE and NOT NULL Constraints on the Foreign Key

When both UNIQUE and NOT NULL constraints are defined on the foreign key, only one row in the child table can reference a parent key value. Because nulls are not allowed in the foreign key, each row in the child table must explicitly reference a value in the parent key.

This model establishes a "one-to-one" relationship between the parent and foreign keys that does not allow undetermined values (nulls) in the foreign key. If you expand the previous example by adding a NOT NULL constraint on the MEMBERNO column of the employee table, in addition to guaranteeing that each employee has a unique membership number, you also ensure that no undetermined values (nulls) are allowed in the MEMBERNO column of the employee table