How to design Relational Database Systems – RDBMS (part 3)

How to design Relational Database Systems (part 3) is a continuation of the previous post

Identifying Attributes (or properties for our entities)

Okay so let’s go ahead now and identify some attributes for our entities or tables. So i said in an earlier post that the attributes we identify in our database design becomes our columns in our physical database.
Okay so let’s start with our customers entities (or table). So ask yourself the simple question: What informatin do we want to store in this table? Obviously you want a unique way to identify each customer’s record, so definitely Customer_id should be our first attribute. How about the customer’s personal data? Yes; so there should be other attributes like: customer_name, customer_phoneno, customer_email, customer_address and so on – depending on whatever information you want to collect about your customers to help your business model, right.

So for our 4 entities, what i can think up looks thus:

Notice that i have left out attributes for sales_details and stocks for now because there is an information which you must understand before enumerating attributes for those two entities. We will define attributes for them when we discuss primary key down somewhere.

Assigning Keys

Primary Keys

Database tables have different kind of keys for different purposes. Earlier i mentioned a column included in an entity or table to uniquely identify each record stored. A primary key column is a column that stores unique data (i.e no two data are the same in such a column) and none of the rows of such a column can be empty.

So for our four entities, we said that Customer_id is the attribute that uniquely identifies each record in the Customers entity – this is our primary key for this entity.

Product_id is the attribute that uniquely identifies each record in the Products entity – this is our primary key for this entity.

Shop_id is the attribute that uniquely identifies each record in the Shops entity – this is our primary key for this entity.

Sale_id is the attribute that uniquely identifies each record in the Sales entity – this is our primary key for this entity.

In rdbms – ERD, we denote the primary key by the symbol PK.

Note that an entity can have more than one attribute combined to be its primary key, such a key combined in such a manner is called a Composite key.

Consider our entity sales_details which was created as a link-entity or associative or go-between entity for sales entity and products entiy respectively. This associative table will have a combination of at least two attributes as its primary key; one key will be the primary key of sales entity and the other key is the primary key of products entity.

Likewise the stocks entity also; one primary key will come from shops and the other from products.

By now i am sure you have already figured out some of the attributes of sales_details and stocks entities, even thought we left it our earlier.

Identifying Foreign Keys

So if i take an attribute which is a primary key in one entity and place as an attribute in another entity, i have created a foreign key in that entity. So the foreign key (denoted by FK in ERD) in an entity is the reference to the primary key of another entity.. Why are my creating a foreign key? i create a foreign key because i want to link up the two entities involved. For instance if i put the primary key of customers entity in sales entity, that will be because i want to know what customers where involved in which sales (its relational database management systems remember – one entity can be related to another).

Identifying data types for our attributes

You can actually get more information on datatypes online expand your knowlegde on more datatype possibilities for your attributes, but for our ERD; our datatypes will be as defined in the diagram:

Common data types include:
CHAR, VARCHAR, TEXT, FLOAT, DOUBLE, and INT

INT stores positive or negative whole number
CHAR can store text – a combination of alphabes, numbers, special characters etc. – CHAR stores fixed length characters, so you have to define the length when using CHAR. For example in MySQL we would mark a column as CHAR thus: CHAR(the the length we expect it not to exceed). For e.g in MySQL if i say CHAR(12), i am telling the database engine; don’t accept to store more that 10 characters here. But note that i may end up storing only 4 characters in a column i marked for storing 12 characters; what happens in this case is that since i used CHAR which stores fixed lenght characters, the database engine fill the remaining 8 spaces for me with spaces.
VARCHAR is same as CHAR, only that VARCHAR only takes as much space as necessary. VARCHAR is also defined with length in most database engine. For instance VARCHAR(255); if i end up using only 55 characters, no other space is taken. I would prefer to use VARCHAR to CHAR if i don’t know roughly how much characters i will be storing.
TEXT contains large amount of text. You don’t have to define the lenght of the text to be stored.
Read about the rest online.

So now that we have understood datatype, our RDBMS – Entity Relationship Diagram now looks like this:

We will continue in the next class with database normalization. See you in the next one.