Question

please use dia to draw the e-r diagram to create Entity - Relationship Diagrams

then use MS access

This project requires you to create a database design. Your design will be documented in a set of Entity-Relationship Crow's Foot diagrams using the representation as shown in the lecture materials. Draw a set of Entity-Relationship diagrams to model the following scenario. The Maggs Realty Company wants to track the Sales Offices that they have across all of Ontario They are interested in being able to maintain information about their agents and properties that are listed by their agents For each Sales Office, the design must be able to store . the office's Street Address the office's City the office's Province (yes, this Ontario, but they might expand someday...) . the office's Postal Code the office's phone number the office's fax number (only if they have a fax machine)) For each Agent, the design must be able to store the agent's first name the agent's last name the agent's cell phone number For each Property listed, the design must be able to store the property Street the property City the property Province (yes, this Ontario, but they might expand someday...) the property Postal Code the size of the property (square meters) All attributes are to be considered as mandatory unless specifically stated differently above. Next, you need to store the tollowingrelationships: Each sales agent is assigned to a sales office A specific sales agent will be designated to manage a sales office. Each property will be listed by a sales agent SPECIFIC DETAILS on the RELATIONSHIPS All Offices have agents assigned to them Each of the agents of The Maggs Realty is assigned to only one of the Sales Offices, A Office may have any number of agents assigned to it. All Offices will have a single agent who will be designated to manage the Sales Office. This agent will (obviously) be one of the agents assigned to that Office. An agent can only manage one Office and not all agents are managers. Agents of The Maggs Realty may list any number of properties at one time. But not every agent will have property listings. All properties in the database will be listed by an agent of The Maggs Realty. The database must store the date each property was listed by the agent. If known, the asking price of the property must also be stored in the database. All attributes are to be considered as mandatory unless specifically stated above All attributes are to be considered as non-unique unless the design specifically requires them to not contain duplicate entries. Project 2: Create a Microsoft Access database Using Microsoft Access, create a database based on your E-R diagram from Project 1 Name this youraccountname_RealEstate.accdb [ replacing youraccountname with your actual user name ] The database must have all the tables required from your design hint, there should be four (4) tables in your database PART 1: a) Create a new table called "AGENT" with the following specifications 1) Unique Agent ID that automatically created when a new item is entered note: this ID must start with YOUR initials: so if your name is Dolly Madision every supply ID would start with DM example: DM0001, where "DM" are YOUR initials DM0002, where "DM" are YOUR initials DM0004, where "DM" are YOUR initials DM0007, where "DM" are YOUR initials hint: MUST be 2 initials and four digits (see examples above) use Autonumber (Long Integer) HINT: If you do not know how to do this, then do an internet search This is good practice for the real world where you are not always supplied with how to complete a task. The solution is very easy to find once you look. NOTE: - very important: if the Agent ID is used as a Foreign Key in another table that key must be of type Long Integer the value entered will just be the number: example: the foreign key is 1 where the Agent ID is DM0001 the foreign key is 2 where the Agent ID is DM0002 the foreign key is 4 where the Agent ID is DM0004 the foreign key is 7 where the Agent ID is DM0007 2) Agent's Last Name maximum 60 characters 3) Agent's First Name maximum 25 characters 4) Agent's Cell Phone Number maximum 10 characters stored as: 5195552323 (no brackets or dashes -no Input Mask or Format) 5) Any other fields based on attributes in your E-R Diagram (if any) Create a new table called "OFFICE" with the following specifications: 1) Office's Street Address - maximum 40 characters 2) Office's City - maximum 25 characters 3) Office's Province maximum 2 characters display in all capital letters (i.e. ON or AB etc.) (must use an Input Mask) 4) Office's Postal Code MUST be 6 characters (onlyl) format: A1B 2C3 (LOL OLO - L: letter 0: number-must use an Input Mask) hint: 0 is zero (0) NOT capital O. hint: LOL is a big hint... 5) Office's Phone Number - maximum 10 characters stored as: 5195552323 (no brackets or dashes -no Input Mask or Format) 6) Office's Fax Number maximum 10 characters stored as: 5195552323 (no brackets or dashes -no Input Mask or Format) 7) Any other fields base d on attributes in your E-R Diagram (if any) Create a new table called "PROPERTY" with the following specifications: 1) Property's Street Address - maximum 30 characters 2) Property's City - maximum 30 characters 3) Property's Province maximum 2 characters display in all capital letters (i.e. ON or AB etc.) (must use an Input Mask) 4) Property's Postal Code maximum 7 characters format: A1B 2C3 (LNL NLN - L: letter N: number - must use an Input Mask) 5) Property's Size number (can have a maximum of two (2) decimal places) 6) Any other fields based on attributes in your E-R Diagram (if any) Create any other tables that are in you E-R Diagram 1)ill in all attributes in your E-R Diagram from any of the tables in your E-R Diagram. PART 2: You must build all the relationships described in your diagram. You must use the Relationships Database Tool in MS Access to signify the relationships. vau mattr le Your name must be the name of the first Agent. You are allowed to make up the names of the other Agent and the Office data. The Property data should be created to appear valid and should make sense in the context of your other data. But, the actual data does not matter as much as demonstrating the ability to enter data into your tables and still maintain referential integrity. So do not overthink or worry about the actual data you Complete the above as required saving the database in your "RealEstate" database. i.e youraccountname_RealEstate.accdb (or.mdb for earlier versions)

Answer 1

What is Data?

In simple words data can be facts related to any object in consideration.

For example your name, age, height, weight, etc are some data related to you.

A picture , image , file , pdf etc can also be considered data.

What is a Database?

Database is a systematic collection of data. Databases support storage and manipulation of data. Databases make data management easy. Let's discuss few examples.

An online telephone directory would definitely use database to store data pertaining to people, phone numbers, other contact details, etc.

Your electricity service provider is obviously using a database to manage billing , client related issues, to handle fault data, etc.

Let's also consider the facebook. It needs to store, manipulate and present data related to members, their friends, member activities, messages, advertisements and lot more.

We can provide countless number of examples for usage of databases .

Click here if the video is not accessible

What is a Database Management System (DBMS)?

Database Management System (DBMS) is a collection of programs which enables its users to access database, manipulate data, reporting / representation of data .

It also helps to control access to the database.

Database Management Systems are not a new concept and as such had been first implemented in 1960s.

Charles Bachmen's Integrated Data Store (IDS) is said to be the first DBMS in history.

With time database technologies evolved a lot while usage and expected functionalities of databases have been increased immensely.

Types of DBMS

Let's see how the DBMS family got evolved with the time. Following diagram shows the evolution of DBMS categories.

Object Oriented Navigational Relational SQL DBMS DBMS DBMS DBMS

There are 4 major types of DBMS. Let's look into them in detail.

• Hierarchical - this type of DBMS employs the "parent-child" relationship of storing data. This type of DBMS is rarely used nowadays. Its structure is like a tree with nodes representing records and branches representing fields. The windows registry used in Windows XP is an example of a hierarchical database. Configuration settings are stored as tree structures with nodes.
• Network DBMS - this type of DBMS supports many-to many relations. This usually results in complex database structures. RDM Server is an example of a database management system that implements the network model.
• Relational DBMS - this type of DBMS defines database relationships in form of tables, also known as relations. Unlike network DBMS, RDBMS does not support many to many relationships.Relational DBMS usually have pre-defined data types that they can support. This is the most popular DBMS type in the market. Examples of relational database management systems include MySQL, Oracle, and Microsoft SQL Server database.
• Object Oriented Relation DBMS - this type supports storage of new data types. The data to be stored is in form of objects. The objects to be stored in the database have attributes (i.e. gender, ager) and methods that define what to do with the data. PostgreSQL is an example of an object oriented relational DBMS.

What is SQL?

Structured Query language (SQL) pronounced as "S-Q-L" or sometimes as "See-Quel"is actually the standard language for dealing with Relational Databases.

SQL programming can be effectively used to insert, search, update, delete database records.

That doesn't mean SQL cannot do things beyond that.

In fact it can do lot of things including, but not limited to, optimizing and maintenance of databases.

Relational databases like MySQL Database, Oracle, Ms SQL server, Sybase, etc uses SQL ! How to use sql syntaxes?

SQL syntaxes used in these databases are almost similar, except the fact that some are using few different syntaxes and even proprietary SQL syntaxes.