Reference no: EM13166766

Create a database.

a.       Make sure to name the database using your last name. For example, smith.accdb.

b.      The database should be in Access 2010 format with .accdb as the file extension. Note that if you used SQL Server, the file extension is .mdf.

2.       Create 4 Table objects.

a.       Field names and table names should be meaningful, appropriate and self-describing. That means the table name should describe the data. For example, a table that contains information about individual songs might be called songs. Do not start the table name with numbers or use any blank spaces in the name or any special characters other than underscore (_).

b.      You may name the fields, tables and views as you like but.

c.       Each table should have 5 fields per table.

d.      Each table should have a variety of data types, such as numeric, text, and date data types. Used several appropriate field data types and properties. Try to configure field properties such as a Default value or mask.

e.      Make sure the tables have a primary key identified. It is customary that the first field you create is your primary key field.

f.        Make sure the tables are related. That means that each table has a primary key that is also found as a foreign key in another table.

g.       Although primary keys are not required to be auto-numbered, this can be helpful in ensuring that each record has a unique primary key. It is strongly recommended for beginners to autonumber the primary key fields in all tables. If you do not, you may run into problems if you use the Visual Studio Wizard tools to help manage your database when you build the web application. Note that in Access the data type is autonumber, but in SQL Server it's called int, with the IsIdentity property set to Yes.

3.       Add data to each of the tables.

a.       There should be at least 5 records per table.   

4.       Create 4 View objects.

a.       For each of the 4 tables, create a View object that contains a SELECT query, which retrieves a subset of data from a single table.

b.      For example, SELECT SongID, SongTitle, ArtistID FROM songs ORDER BY SongID WHERE ArtistGroup = 'Lady Gaga'

c.       Store queries as a View.

d.      Name the View something meaningful such as vSongsByLadyGaga. Notice that the name is prefaced by a letter v. When you are later accessing the views in Visual Studio, it is easier to identify the views if they have a unique name.

e.      Queries run without errors. TEST the database and queries to make sure they work.

f.        Note that in SQL Server databases only SELECT statements can be stored as a View.

5.       Create a JOIN query.

a.       Create at least one View object that contains a SELECT query, which retrieves a subset of data from at least two tables. This query is also known as a JOIN query, because each table is joined to the other using the foreign and primary key fields.

6.       For 1 table, create a query that will allow you to insert a new record in the table. Use a variety of queries including Select, Insert, Delete and Update. Include at least 10 queries in the table stored as views.