Tables Normalization (1 Viewer)

Hi I'm new to access and i don't quite understand it completely, I've been trying for the last week to make a assignment but with no success.(I'm not sure do i have to add new columns in tables for ex no 3 and 4 ? as there is no such fields described in Tables description ) I would appreciate if someone can help me. Here is the assignment details:
A Company eSpace is selling IoT devices, as well as providing a database application for its customers to configure their new installations and access the readings of the sensors.

A customer can buy IoT devices, install them physically, then use the application to define the DEVICES and give them collectively a NETWORK name.

According to the device type, every device keeps measuring every minute and sends its READINGS over the cloud to a central database.

A customer can then go to the application and view reports of the reading history for each device to be able to monitor the hardware asset remotely and online.

The table structure is described below: -

Tables
• Customer - contains details of eSpace’s customer list. Every customer is identified by customerCode. The table also has name, address, city, and phoneNo.
• Device - contains details of the range of available sensor devices that eSpace sells. Each device is identified by a deviceId, name and type and other properties.
• Network - contains details of which customers installed which devices. Each record is identified by NetworkId, the table includes reference to the customer and the device.
• Reading - contains the readings per every installed device in a specific Customer’s network.


2. Create the tables’ physical structure(and Relationships between tables) and comment how you built the tables in 3rd normal form.
3. Write an SQL statement to list customers, their installed networks, and all sensors installed in February 2018, ordered by customer and network.
4. If there is a requirement to know which installed device are active or not, re-write the above SQL statement to list only active installed devices.

We're perfectly willing to help you with your homework, but we're not going to do this for you. Please let us know where you're stuck (I'm guessing #2), what you've attempted, and what results you've received. With that, we can try to point you in the right direction.

In fact, if you're stumped on 2, let me recommend that you read the Tables section of your post carefully. It tells you in the text what the relationships need to be.

i'm stuck on number 2. here is an attachment of what I've done:
Does this make any sense ?

A good way to test your relationships is to convert your image into english and see if the statement is true. For every relationship, start with the 1 side and construct a sentence like this to the many side:

One Customer can have many Networks.
One Device can be in many Networks.
One Network can have many Readings.

If any of those statements are false, your relationship is wrong between them.

Tablewise, I see some issues as well. Mainly, the storing of redundant data. There is no reason for tblReading to duplicate data already in other tables. For example, deviceName is in tblDevice, there is no need to have it in tblReading since your relationships give you access to that data.

Deleted my post because plog's was a bit more concise.

1. The diagram will be neater and make more sense if you move device to the left of network and reading to the right.

2. Reading should NOT contain customerID and DeviceID. Both of those can be obtained by joining to network on networkID

3. reading needs a primary key.

4. When linking SQL Server tables to Access, rename them to remove the "dbo_" prefix. This is unnecessary and actually makes it harder to convert your SQL to T-SQL should you need to because in T-SQL, the tables don't need the dbo priefix but if they use it, it is dbo. rather than dbo_

Once the tables are cleaned up, you can use the query builder to help you to create the SQL. When you open it, the wizard starts. Pick the tables and/or queries you want to add. Access treats queries the same as tables for most purposes and that is why you can never use the same name for both a query and a table. You can name a form "customer" and a table "customer" but if you have a table named "customer", you CANNOT have a query named "customer" because Access needs to use the two object types in the same set and each object needs a unique name.

The relationships should be created on the server side, creating them on the Access side would be cosmetic.


Example: SQL relationship.

their installed networks

Click to expand...

That can be interpreted two ways. Each customer has one network but there are a lot of customers with networks. Or a single customer can have multiple networks like the real world? If it is the latter then networkID cannot be a PK. You would likely need a Network table that lists the Network PK, Network Name, and other unique things about the network. If customer means the person who owns the network then that goes in the table as FK. If it is the person who owns the device then it goes in the device table. Then you current Network table becomes the Junction table Network_Device.

Network_Device_ID 'pk for the assignment of a device to a network
Network_ID_FK 'Foreign key to network table
Device_ID 'Foreign key to device table

Now a single customer can have multiple networks.