You are required to develop Conceptual Data Model for your cellular operator (li

Question

You are required to develop Conceptual Data Model for your cellular operator (like AT&T, Verizon, etc.) including:

Entities with primary attributes

Relationships

Associative entities for relationship attributes

E-R Diagram in Lucidchart

For each entity, attribute and relationship develop name and definition following guidelines from Chapter 2 and present them in 3 tables:

Entity Name

Entity Definition

Attribute Name

Attribute Definition

Is Required?

Is Identifier?

Is Multivalued?

Is Composed?

Relationship Name

Relationship Definition

To develop conceptual data model you can use your plan contract, monthly statements, receipts and other documents as an input.

The data model should encompass the following data:

Customers (SSN, Name, Address, etc.)

Plans (start/end dates, number of min, data size, number of SMS per month, monthly payments, etc.)

Plan Types (individual, family, corporate, etc.)

Handsets (model, S/N, MEID, IMEI, GSM, CDMA, etc.)

For handset included in plan we should be able to find a specific customer using it and his phone number

For handset purchased by customer through operator should have store, purchase date, customer price, full price, tax, etc.

For family plans number of participants usually limited to 5. Ask your provider for that limit number

Usually plan has one plan owner (individual or corporation). Ask your provider what rule they have in this regard.

Plan owner may authorize other plan members to act on his behalf.

Each entity should have at least couple of business attributes and unique identifier.

Entity Name

Entity Definition

Explanation / Answer

as we know that if we want to construct a program firstly we plan what are the attributes and there roles/relationships and we document it as conceptual diagram.

relationships::::
the customer and the handset are in dependency
plan n plan type have dependency
customers n plan assosiation.
handsets n plans assosiation.

SSN---the barcode of identification of phone(primary key)

Name--------name of the customer(not null)

Address-----address of customer (if cdma then not null)

start/end dates----the dates of purchase and validity of warranty respectively

number of min-----the plan validity( not null)

data size-----limit of usage

number of SMS per month----no of messages ,

monthly payments

types of dataplan

individual, family, corporate

phone specifications ::::model(unique key)

S/N foreign key,

MEID, IMEI, GSM, CDMA

as i dont have the tool to create now but the sql queries as

create table customers(ssn varchar2(10) primary key,name char(25) not null,address varchar2(500));

create table plans(strtdate date,enddate date,nomins number(20),datasize varchar2(100),monthlypayment number(40));

create table plantype(ind varchar2(5),family char(3),corporate varchar2(100));

create table handsets(model char(5),s/n varchar2(10) primary key,meid number(5),imel number(15),gsm number(20),cdma number(15) references customers(ssn));

Related Questions

Navigate

• Browse (All)
• Browse Y
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.