SQL Online Tutoring On Conceptual Design

Business Rules

Agents will be asked complete personal information before associating them with the company.
They are requested to give their address, CNIC number and photograph for security and in what area do they operate.
Property type should be mentioned as commercial or presidential in the database.
The clients are also requested complete personal information before processing their any request.
Client’s expertise should be rechecked
Clients can book plots and land for themselves but extra money will be charged if the cancel their booking after 2 weeks.
50% payment should be done in advance and the rest should be payed after complete ownership.

Clients may use their certain credit card to avail any discount if available.
Payment has to be done via cash or card. No online transfer available.
Payment also includes taxes which are government, land and property and any other if applicable.
There are multiple agents who will deal with different clients.
Agent’s shown property should be rechecked if it’s clear from all allegations.

ERD diagram

Description

Clients- This entity has attributes client id, client name, address, DOB, expertise, his investment and the agent id who deal the client. This entity saves the client’s information in the database. One to many relationships with agents and vice versa because one client can have many agents and one agent can deal many clients.

Agents- This entity contains the information of the agents dealing with the clients. The attributes include id, name, address, phone, DOB, expertise and a client id as a foreign key.

Land- This entity has all the attributes regarding the land the client will be buying. The attributes are its id, location, size of the land, and two foreign keys of property id and client id.

Property- This entity has attributes like property id and property type to save the details. Has one to one relationship with land.

Area- As the name shows, this entity saves all the information regarding the properties in a particular area. The attributes are area id, area name and foreign keys of street id and property id. It has one to many relationships with street as one area can have many streets.

Street- This entity marks the information for the property that is on street. The attributes are street id and street location.

Logic Design

Number of tables

Serial Number	Names of the tables
1	Clients
2	Agents
3	Property
4	Land
5	Area
6	Street

Clients:

Attribute	Data Type	Description	Constraints
Client_id	Integer		Primary key
Client_Name	String	Name of the client	Not null
Client _Address	Varchar	Address of the client	Not null
Client_DOB	Varchar	Date of birth of client	Not null
Client_Experience	String	The experience client have of buying land or property	Not null
Client_Investment	Varchar	The amount client will be investing	Not null

Agents

Attribute	Data Type	Description	Constraints
Agent_id	Integer	Id of the agent in database	Primary key
Agent_Name	String	Name of the agent	Not null
Agent_Address	Varchar	Address of the agent	Not null
Agent_Phone	Integer	Phone number of the agent	Not null
Agent_DOB	Varchar	Date of birth of the agent	Not null
Agent_EXP	String	Experience the agent has in this business	Not null

Property:

Attribute	Data Type	Description	Constraints
P_ID	Integer	Property Id in the database	Primary key
P_Type	String	Whether the property is commercial or presidential	Not null

Land:

Attribute	Data Type	Description	Constraints
L_ID	Integer	Land Id in the database	Primary key
L_LOCATION	Varchar	The location of the land	Not null
L_SIZE	Varchar	The size of the land	Not null

Area:

Attribute	Data Type	Description	Constraints
Area_ID	Integer	The area ID	Primary key
Area_Name	String	The name of the area	Not null

Street:

Attribute	Data Type	Description	Constraints
S_ID	Integer	The street ID	Primary key
S_Location	Varchar	The street location	Not null

Normalization

Agents

Agent_ID

Agent_Name

Agent_Address

Agent_Phone

Agent_DOB

Agent_EXP

Clients

Client_ID

Client_Name

Client_Address

Client_DOB

Client_EXP

CLIENT_INVESTMENT

Agent_ID

Property

P_ID

P_Type

Area_ID

Street

S_ID

S_Location

Area_ID

Area

Area_ID

Area_Name

Land

L_ID

L_Location

L_size

P_ID

Implementation and loading

Create table commands:

Agent Table

CREATE TABLE AGENTS(

AGENT_ID INT PRIMARY KEY,

AGENT_NAME VARCHAR(25),

AGENT_ADDRESS VARCHAR(25),

AGENT_PHONE INT,

AGENT_DOB VARCHAR(25),

AGENT_EXP VARCHAR(25)

);

Clients Table

CREATE TABLE CLIENTS(

CLIENT_ID INT PRIMARY KEY,

CLIENT_NAME VARCHAR(25),

CLENT_ADDRESS VARCHAR(25),

CLIENT_DOB VARCHAR(25),

CLIENT_EXP VARCHAR(25),

CLIENT_INVESTMENT VARCHAR(25),

AGENT_ID INT,

CONSTRAINT CLIENTS_AGENT_ID_FK FOREIGN KEY(AGENT_ID) REFERENCES AGENTS(AGENT_ID));

Area Table

CREATE TABLE AREA(

AREA_ID INT PRIMARY KEY,

AREA_NAME VARCHAR(25)

);

Street Table

CREATE TABLE STREETS(

S_ID INT PRIMARY KEY,

S_LOCATION VARCHAR(25),

AREA_ID INT,

CONSTRAINT STREET_AREA_ID_FK FOREIGN KEY(AREA_ID) REFERENCES AREA(AREA_ID));

Property Table

CREATE TABLE PROPERTY(

P_ID INT PRIMARY KEY,

P_TYPE VARCHAR(25),

AREA_ID INT,

CONSTRAINT PROPERTY_AREA_ID_FK FOREIGN KEY(AREA_ID) REFERENCES AREA(AREA_ID));

Land Table

CREATE TABLE LAND(

L_ID INT PRIMARY KEY,

L_LOCATION VARCHAR(25),

L_SIZE VARCHAR(25),

P_ID INT,

CONSTRAINT LAND_P_ID_FK FOREIGN KEY(P_ID) REFERENCES PROPERTY(P_ID));

Select Commands:

SELECT * FROM AGENTS;

SELECT * FROM CLIENTS;

SELECT * FROM AREA;

SELECT * FROM PROPERTY;

SELECT * FROM STREETS;

SELECT * FROM LAND;

Update command:

Left Outer Command:

Equijoin command:

Aggregate with group by command:

Select with where command:

Big Data and Bitcoin

Big data is a term that depicts the huge volume of information – both organized and unstructured – that immerses a business on an everyday premise. Information that is so enormous, quick or complex that it’s troublesome or difficult to handle utilizing customary strategies. The demonstration of getting to and putting away a lot of data for investigation has been around quite a while. Be that as it may, the idea of huge information picked up force in the mid-2000s. The meaning of Big Data as in 3 Vs is:

Volume: Associations gather information from an assortment of sources, including business exchanges, shrewd (IoT) gadgets, modern gear, recordings, online media and that’s just the beginning. Before, putting away it would have been an issue – however less expensive stockpiling on stages like information lakes and Hadoop have facilitated the weight.
Velocity: With the development in the Internet of Things, information transfers in to organizations at an uncommon speed and should be taken care of in an opportune way. RFID labels, sensors and brilliant meters are driving the need to manage these downpours of information in close ongoing.
Variety: Information comes in a wide range of arrangements – from organized, numeric information in customary data sets to unstructured content records, messages, recordings, sounds, stock ticker information and money related exchanges.

The challenges faced by traditional databases is that utilization of a customary information base can prompt vulnerability in information the board. This is on the grounds that it doesn’t have the important instruments to help the activities required by enormous information the executives, for example, quick reaction times.

A lot of technologies currently deal with big data.

NoSQL joins a wide scope of discrete information base advancements that are creating to plan current applications. It portrays a non-SQL or nonrelational information base that conveys a strategy for amassing and recovery of information. They are sent continuously web applications and huge information examination.
R is the programming language and an open-source venture. Alongside it, being utilized by information excavators and analysts, it is broadly actualized for planning measurable programming and for the most part in information examination.
A subpart of enormous information investigation, it attempts to anticipate future conduct by means of earlier information. It works utilizing AI advances, information mining and factual demonstrating and some numerical models to figure future occasions.
With in-assembled highlights for streaming, SQL, AI and diagram preparing support, Apache Spark wins the refer to as the speediest and regular generator for large information change. It underpins significant dialects of huge information containing Python, R, Scala, and Java.

Bitcoin address: A Bitcoin address is an exceptional identifier that fills in as a virtual area where the digital currency can be sent. Individuals can send the digital money to Bitcoin delivers comparably to the manner in which fiat monetary standards can regularly be shipped off email addresses. In any case, the Bitcoin address isn’t expected to be lasting, yet only a token for use in a solitary exchange. In contrast to a computerized wallet, a Bitcoin address can’t hold a parity. The location itself comprises of 26-35 alphanumeric characters. This string is the public portion of an asymmetric key pair. The standard organization for a Bitcoin address is P2PKH (pay to public key hash).

Bitcoin Wallet: A site with Bitcoin is like a physical wallet. Nonetheless, the wallet holds applicable data, for example, the ensured private key used to enter Bitcoin locations and do moves, instead of holding physical cash. Bitcoin wallet types incorporate PC, tablet, organization, and equipment.

Bitcoin transaction: Sending BTC requires approaching the general population and private keys related with that measure of bitcoin (www.bitcoin.com, n.d.). At the point when we talk about somebody “having bitcoins” what we really mean is that individual approaches a key-pair contained:

a public key to which some sum bitcoin was recently sent.
the relating one of a kind private key which approves the BTC recently shipped off the above bar key to be sent somewhere else