Database design

Database design is a complex, but necessary process. It involves creating a functional database system that is able to manage all of a company’s information in one place. When designing a database, there are a lot of factors to consider in order to ensure it can do what is required of it. Let’s discuss a few important factors that need to be considered during the database design process

When planning your database design, you should consider your company’s plan for growth in addition to its current digital storage needs. While it’s important that your database system can support your daily operations now, it’s equally as important to consider how it will be able to support your operations in the future. Because of this, you should work with your SQL database designer to make sure the goal of the software is fully understood.

Along with understanding your company’s plan for growth, this should lead you to decide how flexible the database should be. As new technology is introduced, it’s important that your database is able to easily implement upgrades. By ensuring you invest in reputable SQL database consultant services, you’ll have peace of mind in knowing that your database can easily handle updates that need to be made quickly.

One of the most important factors to consider when designing a database is how often the database will be used. Depending on a company’s needs, a database may only be used for modifying a few entries each day while other databases may see constant adjustments. This is important to consider because the more often the database is used, the higher the chances are of data becoming corrupted or faulty. If your database is going to see heavy use, your SQL database consultant should ensure you have a way of locating and repairing corrupted data.

And lastly, it’s important to think about who is going to be maintaining the database. Common choices include an employee, somebody at your hosting service, or a third-party company. But no matter who you choose, it’s important to know who to contact if something goes awry. Furthermore, you should ensure whoever is maintaining the database is an SQL Server expert and can help you with all of your database needs.

With 26% of businesses having experienced an SQL injection attack in the past 12 months, according to a Small Business Trends survey, it’s more important than ever to invest in SQL database security. By investing in high-quality SQL database consultant services, you can ensure your database works as effectively and efficiently as possible.        

Implementing a Database Design

There is no one correct design for the music scenario posed on the last page. The figure below depicts one design. Note the FK notation beside some of the fields.? FK stands for foreign key and indicates a field whose values uniquely identify rows in another table.? There is no way to designate a field as a foreign key in MS-Access.? For our purposes, you should just remember that building foreign keys into your database is what will enable you to create the table joins needed to answer the questions you envision your database answering.?

Note also the 1 and * labels at the ends of the relationship lines, which indicate the number of times any particular value will appear in the connected field.? As shown in the diagram, values in the Albums table's Album_ID field will appear just once; the same values in the associated field in the Tracks table may?appear many times (conveyed by the * character).? For example, an album with 10 tracks will have its Album_ID value appear in the Albums table just once.? That Album_ID value would appear in the Tracks table 10 times.? This?uniqueness of values in fields is referred to as?cardinality.

Figure 2.2: Instructor's homemade entity-relationship diagram for his version of the?music album review?database design.

If you have questions about this design or see ways to improve it, please share your thoughts in the Lesson 2 Discussion Forum.

In this part of the lesson, we'll create and populate some of the tables depicted in the diagram above to give you a sense of how to move from the design phase to the implementation phase of a database project. Table creation is something that can be accomplished using SQL, as we'll see later in the course. Most RDBMSs also make it possible to create tables through a GUI, which is the approach we will take right now.

An integral part of creating a table is defining its fields and the types of data that can be stored in those fields. Here is a list of the most commonly used data types available in Access:

Text - for strings of up to 255 characters
Memo - for strings greater than 255 characters
Number - for numeric data
AutoNumber - for automatically incremented numeric fields; more on this below
Yes/No - for holding values of 'Yes' or 'No'; equivalent to the Boolean type in other RDBMSs
Date/Time - for storing dates and times

Beyond the data type, fields have a number of other properties that are sometimes useful to set. Among these are Default Value which specifies the value the field takes on by default, and Required, which specifies whether or not the field is allowed to contain Null values. It is also possible to specify that a field is the table's primary key (or part of a multi-field primary key).

Perhaps the most commonly set field property is the Field Size. For Text fields this property defaults to a value of 255 characters. This property should be set to a lower value when appropriate, for a couple of reasons. First, it will reduce the size of the database, and secondly, it can serve as a form of data validation (e.g., assigning a Field Size of 2 to a field that will store state abbreviations will ensure that no more than 2 characters are entered).

When dealing with a Number field, the Field Size property is used to specify the type of number:

Byte - for integers ranging from 0 to 255
Integer - for integers in the range of roughly +/- 32,000
Long Integer - for integers in the range of roughly +/- 2 billion
Single - for real numbers in the range of roughly +/- 1038
Double - for real numbers in the range of roughly +/- 10308

As with Text fields, it is good practice to choose the smallest possible Field Size for a Number field.

With this background on fields in mind, let's move on to implementing the music database.

A. Create a new table
Open Access and from the opening screen click on the?Blank Database?icon.
In the right-hand panel, browse to your course folder and give the database the name?music.accdb.
Click the?Create?button to create the new empty database. Access will automatically create and open a blank table called?Table1.
Select?View?>?Design View?to begin modifying this table to meet your needs.
Give the table the name?Albums.

The Design View provides a grid for you to specify the names, data types and other properties for the fields (columns) you want to have in your table. Note that the table automatically has a field called ID with a data type of AutoNumber. The field is also designated as the table's Primary Key.

Note: Most RDBMSs offer an auto-incrementing numeric data type like this. It's common for database tables to use arbitrary integer fields as their primary key. If you create a field as this AutoNumber type (or its equivalent in another RDBMS), you need not supply a value for it when adding new records to the table. The software will automatically handle that for you.
?
Rename the field from?ID?to?Album_ID.
Beneath that field, add a new one called?Title.

With the 2013 version of MS Access the interface has changed some when it comes to the next two settings. The list of data types has become more streamlined and the way Field Size is set for numeric fields is different. I will indicate below what the differences are.
?
Set the?Title?field's?Data Type?to?Text.

In Access 2013 -?set the?Title?field's?Type?to?Short Text.
?
Set its?Field Size?to?200.? (This and several other options are found in the?Field Properties?section at the bottom of the window, under the?General?tab.? Note that the maximum value allowed for this property is 255 characters.)

In Access 2013 -?no need to set this, the Short Text type holds up to 255 characters.

FYI, the "Long Text" option in Access 2013 ("Memo" option in older Access versions) can hold up to a Gigabyte and can be configured to hold Rich Text formatted information.
?
Repeat these steps to add the other fields defined for the Albums table:

In Access 2013 -?To set the type and size for numeric data, you first choose?Number?from the?Type?list, then under the?General?tab click the?FieldSize?property box. Click the arrow and choose the desired field size (Integer, Long Integer, Float, etc.).
Table 2.8: Albums Table
Name	Type
Album_ID	AutoNumber
Title	Text (200)
Artist_ID	Long Integer
Release_Year	Integer
Label_ID	Long Integer
?
Note:

Try adding a field with a name of?Year?to see why we used the name?Release_Year?instead.

When finished adding fields to the Albums table, click the?Save?button.
To add the next table to the database design, click the?Create?tab, then?Table Design.
Repeat the steps outlined above to define the?Artists?table with the following fields:
Table 2.9: Artists Table
Name	Type
Artist_ID	AutoNumber
Artist_Name	Text (200)
Year_Begin	Integer
Year_End	Integer
Before closing the table, click in the small gray area to the left of the?Artist_ID?field to select that field then click on the?Primary Key?button on the ribbon.
Create the?Labels?table with the following fields:
Table 2.10: Labels Table
Name	Type
Label_ID	AutoNumber
Label_Name	Text (200)
Set?Label_ID?as the primary key of the?Labels?table.
B. Establish relationships between the tables

While it's not strictly necessary to do so, it can be beneficial to spell out the relationships between the tables that you envisioned during the design phase.

Click on?Database Tools?>?Relationships.
From the?Show Table?dialog double-click on each of the tables to add them to the?Relationships?layout.
Arrange the tables so that the?Albums?table appears between the?Artists?and?Labels?tables.
Like you did when building queries earlier, click on the?Artist_ID?field in the?Artists?table and drag it to the?Artist_ID?field in the?Albums?table. You should see a dialog titled "Edit Relationships".
Check each of the three boxes:?Enforce Referential Integrity,?Cascade Update Related Fields, and?Cascade Delete Related Records.

Checking these boxes tells Access that you want its help in keeping the values in the Artist_ID field in sync. For example:
Let's say your Artists table has only three records (with Artist_ID values of 1, 2 and 3). If you attempted to add a record to the Albums table with an Artist_ID of 4, Access would stop you, since that value does not exist in the related Artists table.
If you change an Artist_ID value in the Artists table, all records in the Albums table will automatically have their Artist_ID values updated to match.
If you delete an artist from the Artists table, the related records in the Albums table will be deleted as well.
Without implementing these referential integrity settings, you run the risk of inaccuracies creeping into your database over time.
Click the?Create?button to establish the relationship between?Artists?and?Albums.
Follow the same steps to establish a relationship between?Albums?and?Labels?based on the?Label_ID?field.
Save?and?Close?the?Relationships?window.n