Skip to main content

DAT 601 - Robin Le Couteur - 3/27/2018

Friday, March 23, 2018

Class Reflection Transforming Conceptual to Logical

Many to Many Relationships

Many to many relationships do not exist in a database, so there must be a way to model that sort of relationship. Many to many can exist in a conceptual model but you have to remove direct many to many relationships in a logical model. An example of how this might work is a many to many relationship between student and course. 
Many students can be part of many courses and many courses can have many students.

What you would do is add a table in between the two tables called StudentCourse. It will contain the courseID and Student ID and additional info as required. The 2 tables will each have a one to many relationship to the StudentCourse table

Conceptual example


Logical example

As the example shows, a connecting table is added and the many to many relationship is removed. This model allows many students to be related to many courses and vice versa without the existence of an actual many to many relationship.

One to One

When transforming to logical, in a one to one relationship, the ID from the either side of the relationship gets added as a new column to the other side of the relationship as a foreign key.

One to Many

When transforming to logical, in a one to many relationship, the ID from the One side of the relationship gets added as a new column to the Many side of the relationship as a foreign key.









Superclass subclass relationships

Create one relation for the super type that holds all the common attributes for the subtypes.
Or create one relation for each subtype.



Comments

Post a Comment

Popular posts from this blog

DAT 601 - Robin Le Couteur - Class reflection - 11/05/2018

Friday, May 11, 2018 Class Reflection In this class we covered some useful SQL queries. GROUP BY The first statement we covered was the GROUP BY statement. This statement groups results under the specified columns like this: SELECT Country, Region, SUM(Sales) As TotalSales FROM Sales GROUP BY Country, Region; This statement will group the sum of sales per region, and each region will be grouped under the country it belongs to. There is also GROUP BY ROLLUP and GROUP BY CUBE statements which group them differently. You can also group GROUP BY statements together with a GROUPING SET statement. It is a simplified version of a UNION that concatenates GROUP BY statements into one table of results. VIEW A view is a useful for giving certain users a certain view of the database. In a view you can set the tables and columns that are able to be seen in that view, then if someone uses that view they can only access the data available in the view. It...
Post a Comment
Read more

DAT 601 - Robin Le Couteur - Class reflection - 12/06/2018

Friday, June 12, 2018 Class Reflection In this class we looked at how databases store files There is a master database called the mdf that stores all the information on the database and where to find your databases in the database server. The next file is the ndf file that stores user created databases. These ndf files are what your actual database is stored in. The mdf just tells you where to find your databases and other system information etc.
Post a Comment
Read more

DAT 601 - Robin Le Couteur - NaLER Analysis - 10/4/2018

Tuesday, April 10, 2018 NaLER Analysis In this class we looked at how to do a NaLER Analysis Here's some of the rules I picked up Step 1 - Determine the format of the model and make sure the model is consistently in that format Step 3 (For each entity) - 3.1 Construct a sentence for the primary key attribute/s of each entity: "Each <entity name> is uniquely identified by <primary key>" Make sure the primary key actually makes sense as the PK of the entity in question 3.2 One <E-name> identified by <PK> must have one <attribute name> 3.3 One <E-name> identified by <PK> <optionality><relationship-name><cardinality><E-name2> identified by <foreign key> One Zoo(Zoo_no) may house one or more Animal (Zoo_no, Animal_no) Step 4 (Populate the sentences from 3.2, 3.3 with these examples) - Zoo111 houses animal 'regents park 123' Zoo111 has the name 'reg...
Post a Comment
Read more