Forem: Lenique Noralez

Networking via Twitter & Dev.to

Lenique Noralez — Sat, 11 May 2019 02:04:47 +0000

I've read a lot of post on twitter with people saying that twitter is a very powerful networking tool. I've had a twitter account but I don't really use it, all I do is read people tweets and retweet.

So I'd like to start a discuss to hear how different people network using twitter, dev.to or other social network and also in real life (Meetups, Hackathons etc.).

Networking is something that is pretty hard for me. I recently moved to St.Louis from Belize and I've found it hard to network and meet other developers. I'm the only Developer at the company I'm working at so I'm not really connecting with other developers.

So if any one have any great gems on networking I'd love to heard them.

DBMS For Application Development: SQL (Structured Query Language)

Lenique Noralez — Mon, 08 Apr 2019 03:24:08 +0000

Photo by Chris Liverani on Unsplash

SQL is a programming language used to store, retrieve and manipulate data in a database.

SQL is also used to define the schema of a database. When defining the schema for a table you can specify:

Data type of each attribute in the table
The constraints on the attribute
Name of each table column

The basic data types of SQL are:

char(short for character) - a fixed length string. The length is specified by the user.
varchar(short for character varying) - is a variable length with a user defined max length
int - an integer
smallint - a smaller integer
numeric - a fixed point number with a user defined precision number.
real, double precision - floating-point and double-precision floating point numbers
float - a floating point number with a user defined precision

int, smallest, real and double precision data type are machine dependent. The max length and precision of these data type are defined by the computer architecture on which the databases is stored.

To create the schema for a table in SQL we use the create table command.
Let’s define the the schema for a few tables in our photo sharing application.

CREATE TABLE user(
    user_id INTEGER NOT NULL AUTO_INCREMENT,
    username VARCHAR(30),
    password VARCHAR(30),
    email_add VARCHAR(32),
    fullname VARCHAR(30),
    fb_id INTEGER,
    twitter_id INTEGER,
    profile_pic_url VARCHAR(30),
    privacy_level BOOLEAN,
    tag_option BOOLEAN,
    primary key (user_id, username, email_add),
);

CREATE TABLE followers(
    uid INTEGER
    follower_id INTEGER
    timestamp DATE
    rejectdenyoption INTEGER,
    foreign key (uid) references user
);

CREATE TABLE following(
    uid INTEGER
    follower_id INTEGER
    timestamp DATE
    rejectdenyoption INTEGER,
    foreign key (uid) references user
);

To insert data into our newly created tables we use:

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

We can omit the column name:

INSERT INTO table_name
VALUES (value1, value2, value3, ...);

But we must put the value in the order as defined in the table schema.

Let's add a few records to our user table.

INSERT INTO table_name (username, password, email_add, fullname, fb_id, twitter_id, profile_pic_url, privacy_level, tag_option)
VALUES (Jame_Bond_007, fakepassword, 'bond_james@hotmail.com' , 'Jame Bond' , 1847123, 1847123, 'https://cdn.photoshare.com', true, false);

INSERT INTO table_name (username, password, email_add, fullname, fb_id, twitter_id, profile_pic_url, privacy_level, tag_option)
VALUES (LilLeafHurricane, fakepassword, 'rocklee@gmail.com' , 'Rock Lee' , 3547126, 3547126, 'https://cdn.photoshare.com', true, false;

Now we have data in our table. We need to retrieve that data to show it in our application. A query is a request to get data from our database. An SQL query consists of three parts, select, from, and where. The from clause tells us which tables we are querying. The select clause tell us which columns we want to output from our query results. The where clause is used to set condition on our query output.

Let’s do a few query on our photo sharing application database.

-- Select all user with private account
select username, email_add, profile_pic_url from user where privacy_level = true;

-- Get a specific user data
select * from user where username = 'username';

-- Get all user photo [Return only photo_id],user id is random
select photo_id from photo where uid = 98494

-- Get a specific user follower [Return follower id]
select follower_id from followers where uid = 64736 and follower_id = 12342

-- Get a specific user follower [Return follower id]
select follower_id from followers where uid = 64736 and follower_id = 12342

SQL provides aggregation functions, aggregation functions takes a collection as input and return a single value.

Average: avg
Minimum: min
Maximum: max
Total: sum
Count: count

Of all the aggregation function sum and avg can only have numbers as input.

Here are a few examples using aggregation functions.

-- Count all likes on a specific photo
select sum (photo_id) as likes from likes where photo_id= 090982;

-- Count all user followers
select sum (uid) as likes from followers where uid = 090982;

-- Count all people who user is following
select sum (uid) as likes from following where uid = 090982;

There’s a lot of use case for the other aggregation function but to keep the examples simple I use sum since it’s use case is very simple in this application.

SQL provides the set operations intersect, union and except that allows you to perform set operations on two tables. The union operations combines the result of two queries into a single table. Any duplicates are eliminated. The intersect operation takes the the results of two queries and return only the records that are in both query result. The except operation takes the unique rows of a query results and returns the rows that do not appear in the second result set, except does not remove duplicates.

Here is an example of a set operation using intercept to get all the liked photos two users share in common:

-- Return the photo_id of all the images two user both like
SELECT photo_id
FROM   likes
WHERE  likerUID = 12345;
INTERSECT
SELECT photo_id
FROM   likes
WHERE  likerUID = 67890;

SQL is a programming language used to interact with data in a database. SQL have special functions called aggregate functions that you can use to apply to a query results to returns a single output. You can perform set operations on the result of multiple queries to be combined into a single result.

Useful Resources:

SQL - Full course for beginners

DBMS For Application Development: Relational Model & Relational Database

Lenique Noralez — Mon, 08 Apr 2019 03:24:02 +0000

Photo by Carlos Muza on Unsplash

A data model is an abstract model used to organize your application data and describes how your data relate to one another and real world objects.
There are many types of data models but the most widely used one is the relational model due to its simplicity.

The relational model represents data in tables and are grouped into relationships. A database that uses the relational model is known as a relational database.

The tables in a relational database is assigned a unique name, usually a name that describe what’s going to be stored in the table.

Below is diagram showing the use of the relational model to describe the database structure and relationship of data in a photo sharing application. This diagram shows how data is related and separated into different tables. Properly modeling your data is powerful in relational databases because it reduces the chances of data being duplicated and redundant data being retrieved.

The diagram above is know as a schema diagram. A schema diagram is a visual representation of the database schema that shows the relationship in the database, table attributes, primary key and foreign key.

The schema is the logical design of the database, it includes the table attribute, attribute type and primary and foreign key constraints.

The primary key is a set of one or more attribute whose values are used to uniquely identify rows in a table. For example the user_id, username and email in the users table are unique for each user record in the table, these three fields are used to uniquely identify a user.

A foreign key is a set of attributes that identifies a record in another table, the foreign key in the referencing table is always the primary key in the referenced table. A foreign key in the above data model would be the uid in the followers table that references a user in the user table.

Building an application that uses a relational database requires a lot of thinking ahead to ensure data is retrieved efficiently.

Useful Resources

The Relational Model [YouTube Video]

Database Management Systems For Application Development: Introduction

Lenique Noralez — Sun, 10 Mar 2019 03:45:49 +0000

Photo by imgix on Unsplash

At the heart of all application is the database where all data related to the application is stored.

In a series of posts I’ll be discussing how Database Management Systems are used in applications development.

In this introductory post I’ll be giving a quick overview of some Database terms.

Whats is a Database Management Systems?

A Database Management Systems is a software used to properly store, retrieve and update data in a database. A database is a collection of interrelate data.

A view is a subset of data returned from a query (A query is a request for data from a database). A view hides unnecessary details away from users and provide security to prevent users from accessing restricted data.

The structure of data is very important in database management systems. The overall design of a database is called a Schema. Schema describes the logical design of a database. Schemas are the blueprint of how the database will be constructed.

The data model is a way to describe your data, relationships in your data and data consistency and constraints. There are four type of data models:

Relation Model
Entity-Relationship Model
Object-based Data Model
Semistructured Data Model

The Relation Model and the Entity-Relationship Model are the most widely used data models.

Database System provides a data-definition language that is used to specify the database schema and a data-manipulation language that is used to interact with the database. These are not two separate languages, they form a single database language, such as the widely used SQL (Which we will be discussing in a later post).

DBMS needs to ensure data is safe in the event of crashes and against unauthorized access. Since data in application are usually shared among multiple users, the DBMS must avoid inconsistent result.

Application often perform multiple operations on the database that count as one unit of work. Transaction must hold the following set of properties:

Atomicity
Consistency
Isolation
Durability

These properties guarantees reliability even in the event of errors or power failure.Transactions that are performed concurrently must be executed with out conflict.

Data is important to every application. The database can be considered the heart of an application.

In the coming series of posts I will be going in depth about different database topics using a well-know application as an example.